excel - ExecuteExcel4Macro ，有没有办法通过索引而不是 Application.ExecuteExcel4Macro 中的名称来引用工作表

Question

我想在不打开工作簿的情况下通过索引方法引用工作表的工作表。但是，我不知道该怎么做。网上的许多例子都是使用“Sheet1”，这不是我的情况。就我而言，每个工作簿都有其工作表名称，它们的名称都不同。

Sub excelfor_macro()
Dim p As String, f As String
Dim s As String, a As String
Dim my_array(1 To 8) As String
Dim r As Integer

Application.ScreenUpdating = False
p = "C:\Users\puntek\Desktop\"
f = "490XXZMAV31002S84D6A_002S84D68.xlsx"
s = SHEETNAME(1).Name  **'This is where, i want to reference by index, not worksheet name**

For r = 1 To 8
    a = Cells(r + 8, 3).Address 'start from row 9 the info
    my_array(r) = GetValue(p, f, s, a)
Next r
End Sub

Private Function GetValue(path, file, sheet, ref) 'This is getvalue function, the worksheetname will pass to sheet
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
    GetValue = "File Not Found"
    Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function

标签： excelvbaexcel-4.0

Answer 1

打开工作簿以获取名称：

Dim wb as Workbook
p = "C:\Users\puntek\Desktop\"
f = "490XXZMAV31002S84D6A_002S84D68.xlsx"
Set wb = Workbooks.Open(p & f) ' or Set wb = Workbooks.Add(p & f)
s = wb.Worksheets(1).Name
' optionally close the workbook
wb.Close