excel - 关闭工作簿时“对象变量或未设置块变量”

Question

我正在尝试修复一些代码，但没有使用 Microsoft Visual Basic 的经验。关闭活动工作簿时出现运行时错误“91”，导致错误的代码是：

Set appExcel = New Excel.Application
appExcel.Visible = True

' More VBA code here, I can post if it would be necessary but I would 
' assume this is enough code to see the problem

appExcel.ActiveWorkbook.Close
appExcel.Quit

我假设此时 Excel 活动工作簿将关闭并且不知道为什么此代码不这样做

标签： excelvba

Answer 1

您收到该错误是因为此时该实例中没有打开工作簿。

如果您尝试此代码，您将看到不会出现错误

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True

'
'~~> Rest of the code
'
appExcel.Workbooks.Add '<~~ Add a temp workbook
appExcel.ActiveWorkbook.Close

要处理它，请使用此

Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True

'
'~~> Rest of the code
'
If appExcel.Workbooks.Count > 0 Then appExcel.ActiveWorkbook.Close

最好是使用，Objects这样您就不会错误地关闭错误的工作簿。例如

Dim wb As Workbook
Set wb = appExcel.Workbooks.Add
'
'~~> Rest of the code
'
If Not wb Is Nothing Then wb.Close
Set wb = Nothing