vba - Excel VBA - 关闭工作簿

Question

我正在将工作表从另一个工作簿导入到我当前的工作簿中。完成工作表的导入后，我想关闭其他工作簿。我使用的代码给出错误运行时错误9':下标超出范围。

Sub ImportWorksheet(MyPath As String, wbName As String)

ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=MyPath
Sheets(1).Copy After:=Workbooks(ControlFile).Sheets(1)
ActiveSheet.Name = wbName
Workbooks(MyPath).Close SaveChanges:=False
Windows(ControlFile).Activate

End Sub

我也尝试过使用

Windows(MyPath).Activate
ActiveWorkbook.Close SaveChanges:=False

但我遇到了同样的错误。

Answer 1

由于 Workbooks 对象的 Open 方法返回一个 Workbook 对象，您可以引用打开的工作簿:

Sub ImportWorksheet(MyPath As String, wbName As String)
    ControlFile = ActiveWorkbook.Name
    With Workbooks.Open(Filename:=MyPath)
        .Sheets(1).Copy After:=Workbooks(ControlFile).Sheets(1)
        .Sheets(2).Name = wbName
        .Close SaveChanges:=False 
    End With 
End Sub