CODEWITHSUNDEEP
excelvbscript
Parag
Parag

Reputation: 13

How to exit gracefully closing open excel file on error?

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

Thanks in advance

Upvotes: 1

Views: 5571

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200213

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

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.

The class can be used like this:

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

Upvotes: 2

Related Questions