递归火山软件开发平台

标题: 利用Excel宏录制在火山PC中使用代码COM对象代替Excel窗口... [打印本页]

作者: 路亦洲 时间: 2023-2-19 03:21
标题: 利用Excel宏录制在火山PC中使用代码COM对象代替Excel窗口...
本帖最后由路亦洲于 2023-2-19 03:29 编辑

使用COM对象操作Excel时，有时会碰到有些Excel窗口操作在火山视窗中是怎么样通过代码实现的呢？在这里我推荐一种方法：就是利用Excel的宏功能，先录制好该操作的宏，再查看宏，把VBA代码复制保存到记事本（至于不会录制宏的请自行bai度）。下面我举例一下：Excel双列排序（满足两个条件的排序）
录到的VBA代码：
Sub 排序()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C513" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("D2

513" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1

513")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
End With
End Sub

是面有几介对象我之前没有见过，但是我可以利用易语言中的对象.查看()一层层的往下查，比如第一行的“Sort”对象是“Sheet”（工作表）的子对象，在易语言中就是工作表对象.读对象型属性("Sort")，取到“Sort”对象，再执行代码：Sort.查看().并且把弹出的对象的属性、方法保存到txt。这样就能一层层查到相关的对象的属性和方法。SortFields则是Sort的子对象，以此类推。
上面VBA代码中很多属性（参数）都是Excel独有的。但是可以对照下面的Excel属性（参数）常数，在火山视窗中直接用整数，而不需用变体型（除非是实在不能省略的参数，又找不到常数，但是在易语言中是可以省略的参数，在火山pc才用一个空值的变体型）
xlAbove = 0
xlBelow = 1
xlSolid = 1
xlFirst = 0
xlLast = 1
xlLastCell = 11
xlTopToBottom = 1
xlLeftToRight = 2
xlGeneral = 1
xlAutomatic = -4105
xlFormats = -4122
xlNone = -4142
xlCenter = -4108
xlDistributed = -4117
xlJustify = -4130
xlBottom = -4107
xlLeft = -4131
xlRight = -4152
xlTop = -4160
xlRTL = -5004
xlLTR = -5003
xlContext = -5002
xlFillDefault = 0
xlFillCopy = 1
xlFillSeries = 2
xlFillFormats = 3
xlFillValues = 4
xlFillDays = 5
xlFillWeekdays = 6
xlFillMonths = 7
xlFillYears = 8
xlLinearTrend = 9
xlGrowthTrend = 10
xlFlashFill = 11
xlAnd = 1
xlOr = 2
xlTop10Items = 3
xlBottom10Items = 4
xlTop10Percent = 5
xlBottom10Percent = 6
xlFilterValues = 7
xlFilterCellColor = 8
xlFilterFontColor = 9
xlFilterIcon = 10
xlFilterDynamic = 11
xlDiagonalDown = 5
xlDiagonalUp = 6
xlEdgeLeft = 7
xlEdgeTop = 8
xlEdgeBottom = 9
xlEdgeRight = 10
xlInsideHorizontal = 12
xlInsideVertical = 11
xlHairline = 1
xlThin = 2
xlThick = 4
xlMedium = -4138
xlCellTypeConstants = 2
xlCellTypeBlanks = 4
xlCellTypeLastCell = 11
xlCellTypeVisible = 12
xlCellTypeFormulas = -4123
xlCellTypeComments = -4144
xlCellTypeAllFormatConditions = -4172
xlCellTypeSameFormatConditions = -4173
xlCellTypeAllValidation = -4174
xlCellTypeSameValidation = -4175
xlColorIndexAutomatic = -4105
xlColorIndexNone = -4142
xlCopy = 1
xlCut = 2
xlShiftUp = -4162
xlShiftDown = -4121
xlShiftToLeft = -4159
xlShiftToRight = -4161
xlUp = -4162
xlDown = -4121
xlToLeft = -4159
xlToRight = -4161
xlCSV = 6
xlHtml = 44
xlWorkbookDefault = 51
xlOpenXMLWorkbook = 51
xlOpenXMLWorkbookMacroEnabled = 52
xlWorkbookNormal = -4143
xlCurrentPlatformText = -4158
xlTypePDF = 0
xlTypeXPS = 1
xlQualityStandard = 0
xlQualityMinimum = 1
xlFormulas = -4123
xlComments = -4144
xlValues = -4163
xlContinuous = 1
xlDashDot = 4
xlDashDotDot = 5
xlSlantDashDot = 13
xlDash = -4115
xldot = -4118
xlDouble = -4119
xlLineStyleNone = -4142
xlHorizontal = -4128
xlVertical = -4166
xlDownward = -4170
xlUpward = -4171
xlPasteValues = -4163
xlPasteComments = -4144
xlPasteFormulas = -4123
xlPasteFormats = -4122
xlPasteAll = -4104
xlPasteValidation = 6
xlPasteAllExceptBorders = 7
xlPasteColumnWidths = 8
xlPasteFormulasAndNumberFormats = 11
xlPasteValuesAndNumberFormats = 12
xlPasteAllUsingSourceTheme = 13
xlPasteAllMergingConditionalFormats = 14
xlSheetVisible = -1
xlSheetHidden = 0
xlSheetVeryHidden = 2
xlNumbers = 1
xlTextValues = 2
xlLogical = 4
xlErrors = 16
xlSortNormal = 0
xlSortTextAsNumbers = 1
xlPinYin = 1
xlStroke = 2
xlAscending = 1    //Excel排序，1为升序
xlDescending = 2 //Excel排序，2为降序
xlManual = -4135
xlSortColumns = 1
xlSortRows = 2
xlSortOnValues = 0
xlSortOnCellColor = 1
xlSortOnFontColor = 2
xlSortOnIcon = 3
xlSortValues = 1
xlSortLabels = 2
xlUnderlineStyleNone = -4142
xlUnderlineStyleDouble = -4119
xlUnderlineStyleSingle = 2
xlUnderlineStyleSingleAccounting = 4
xlUnderlineStyleDoubleAccounting = 5
xlGuess = 0
xlYes = 1
xlNo = 2

下面请看翻译到火山视窗的代码：(易语言也可以用此方法)
(, 下载次数: 14)

作者: Inverse 时间: 2023-2-20 21:31
谢谢分享
可以的话提倡分享.v源码文件

作者: 路亦洲 时间: 2023-2-20 22:40

Inverse 发表于 2023-2-20 21:31
谢谢分享
可以的话提倡分享.v源码文件

拿去吧 (, 下载次数: 1)

欢迎光临递归火山软件开发平台 (https://bbs.voldp.com/)