vba - 在 VBA 中导入工作表时出错

Question

我正在尝试编写一个 VBA 宏来从另一个工作簿导入工作表。我在“复制”行收到“下标超出范围”错误。该文件正确打开，但我不确定之后出了什么问题。

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim lastdate As String, filename As String

lastdate = Format(sheet_1.Range("D11") - 7, "ddmmyy")
filename = "C:\Dir\file " & lastdate & ".xlsm"

Workbooks.Open (filename)
Workbooks(filename).Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)

Workbooks(filename).Close

Application.ScreenUpdating = True
Application.DisplayAlerts = False

End Sub

编辑:错误更改为“下标超出范围”，代码已更改，因此索引引用的工作表。

代码 v2:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim lastdate As String, filename As String

lastdate = Format(sheet1.Range("D11") - 7, "ddmmyy")
filename = "C:\Dir\file " & lastdate & ".xlsm"

Dim wbk As Workbook
wbk = Workbooks.Open(filename)

wbk.Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)
wbk.Close

End Sub

Answer 1

您得到下标超出范围错误，因为您没有工作簿的 Name正确的。 Name与完整路径不同(这是您一直在假设的)；它与文件名相同。

这将起作用:

Workbooks.Open "C:\Dir\file1234.xlsm"
Workbooks("file1234.xlsm").Sheets(1).Range("a1").Value = "yay"

而这不会:

Workbooks.Open "C:\Dir\file1234.xlsm"
Workbooks("C:\Dir\file1234.xlsm").Sheets(1).Range("a1").Value = "yay"

但是引用工作簿的更好方法是像这样设置对它的显式引用:

Dim wbk As Workbook
Set wbk = Workbooks.Open(filename)
wbk.Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)

与 wbk您现在可以处理所需的工作簿；你不必猜测它的名字或任何东西。