如何在出错时正常退出关闭打开的 excel 文件? [英] How to exit gracefully closing open excel file on error?

问题描述

我使用 dos .bat 文件连续运行 .vbs 文件...如果发生任何错误，批处理文件会再次运行 vbscript 并继续运行.该脚本通过互联网连接到一个站点以执行一些 api 调用.现在，当出现连接问题或任何其他错误时，控制来自脚本，保持 Excel 文件打开.这样，每个错误都会打开许多​​excel文件......代码如下......请建议我如何在错误时关闭excel文件然后优雅地退出脚本.

I use dos .bat file to run a .vbs file continuously ... If any error occurs the batch file runs the vbscript again and it goes on. The script connects via internet to a site to execute some api calls. Now when there is connection problem or any other error then the control comes out of the script keeping the excel file open. This way many excel files gets open on each error ... The code is as follows ... please advice me how to close the excel file on error and then come out of the script gracefully.

       '{{{{Some coding}}}}

dim objExcel, objWorkbook, objRange
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:	emp.xlsx")
objExcel.Visible = True

       '{{{{Some coding}}}}

objExcel.Cells(xlrow, 3).Value="Test"
objExcel.Cells(xlrow, 3).Select
objExcel.Activecell.Show

       '{{{{Some coding}}}}

objExcel.Workbooks(1).save
objExcel.Workbooks(1).close
objExcel.Quit
Set objExcel = Nothing
Set objWorkbook = Nothing
WScript.Quit

提前致谢

推荐答案

一种可能的方法是将 Excel 处理包装在自定义类中:

One possible approach is to wrap the Excel handling in a custom class:

Class Excel
  Private xl

  Private Sub Class_Initialize
    Set xl = CreateObject("Excel.Application")
  End Sub

  Private Sub Class_Terminate
    For Each wb In xl.Workbooks
      wb.Saved = True  'discard unsaved changes
      wb.Close         'close workbook
    Next
    xl.Quit            'quit Excel
  End Sub

  Public Function OpenWorkbook(filename)
    Set OpenWorkbook = xl.Workbooks.Open(filename)
  End Function

  Public Function NewWorkbook
    Set NewWorkbook = xl.Workbooks.Add
  End Function

  Public Property Get Workbooks
    Set Workbooks = xl.Workbooks
  End Property
End Class

每当类实例被销毁时，都会自动调用过程 Class_Terminate.这样您就可以自动关闭打开的工作簿并退出 Excel.

The procedure Class_Terminate is automatically called whenever a class instance is destroyed. That way you can automatically close the open workbooks and quit Excel.

类可以这样使用:

Set xl = New Excel
Set wb = xl.OpenWorkbook("C:path	oyour.xlsx")
...
wb.Close

这篇关于如何在出错时正常退出关闭打开的 excel 文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！