如何退出正常关闭打开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:\temp.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处理包装在自定义类中： / p>

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\to\your.xlsx")
...
wb.Close

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