如何在VBScript中使用Excel工作表功能? [英] How to use Excel worksheet functions in VBScript?

问题描述

我需要计算Excel的列A中的活动单元格数.
我可以在Excel VBA中使用"worksheetfunction.countA"轻松实现此目的，但无法在VBScript中获得相同的效果.

I need to count the number of active cells in column A of Excel.
I can achieve this easily using 'worksheetfunction.countA' in Excel VBA, but unable to get the same in VBScript.

我尝试了以下代码:

Dim objXl , objWorkbook, objSheet ,numofactivecells

Set objXl = createobject("Excel.Application")

set objWorkbook= objXl.Workbooks.open("C:\Users\Username\Desktop\filename.xlsm")
'change filename

set objSheet = objWorkbook.Worksheets(1)

objXl.visible = true

objsheet.cells(1,1).select

numofactivecells = objsheet.WorksheetFunction.CountA(Range("A:A"))

msgbox numofactivecells

我需要对包含在变量中存储在列A中的数据的单元格进行计数.

I need count of cells containing data in column A stored in variable.

执行代码时，我收到以下错误消息:

I get the following error messages when I execute the code:

Microsoft VBScript编译错误:预期的标识符

Microsoft VBScript compilation error: Expected identifier

Microsoft VBScript编译错误:预期为')'

Microsoft VBScript compilation error: Expected ')'

推荐答案

一些错误:

1. WorksheetFunction是Excel.Application对象的方法，而不是Worksheet.
2. Range不能单独使用，它是Worksheet对象的方法.

1. WorksheetFunction is a method of the Excel.Application object, not Worksheet.
2. Range can't be used by itself, it's a method of a Worksheet object.

下面的代码将起作用:

Dim objXl
Dim objWorkbook
Dim objSheet
Dim iActiveCells

Set objXl = CreateObject("Excel.Application")
Set objWorkbook = objXl.Workbooks.open("C:\Temp\test2.xlsx") 'change filename
Set objSheet = objWorkbook.Worksheets(1)

objXl.Visible = True

With objSheet
    .Cells(1, 1).Select
    iActiveCells = objXl.WorksheetFunction.CountA(.Range("A:A"))
End With

MsgBox iActiveCells

这篇关于如何在VBScript中使用Excel工作表功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！