Closing Excel file

[Dave_D]

I have the following code which loops thru cells until the condition is true. When the condition is true, I branch out of the loop to close the Excel file. The err.number is 0 in all events, but the file does not be closed upon checking the task manager.



Set oXl = CreateObject("Excel Application")
oXl.Workbooks.Open(Myfile)

Do Until x = " " or x_cnt = 100
If oXl.ActiveCell.Offset(R, C).Value <> Empty Then
x = oXl.ActiveCell.Offset(R, C).Value
x_cnt = x_cnt + 1
Else
Goto Exit1
End If
......
Loop

Exit1:
....
Msgbox (err.number) is 0 entering these next statements
oXl.Application.Quit
Msgbox (err.number) still 0
Set oXl = Nothing
Msgbox (err.number) still 0

I've even tried oXl.Close(Myfile) and when I leave this statement as a part of the code, oXl.ThisWorkBook.Saved = True, err.number = 91. I've tried False as well, but still get err 91. File is task manager remains open.

[ranman256]

oxl.ActiveWorkbook.Close False
oxl.application.Quit

[John_G]

It might be because you are doing the "Quit" first. This is sequence of events that I use:

  Dim xlapp As Excel.Application
  Dim xlBook As Excel.Workbook, xlSheet1 As Excel.Worksheet, xlSheet2 As Excel.Worksheet
  '
  '  other code here
  '
  Set xlapp = CreateObject("excel.application")
  Set xlBook = xlapp.Workbooks.Open(filename)
  '
  ' lots of code here
  '
  xlBook.Save
  xlBook.Close
  xlapp.Workbooks.Close
  xlapp.Quit
  Set xlapp = Nothing

It has worked so far.

[ssanfu]

I use the same order as John_G to close/quit Excel using automation. But I have had problems with Excel actually quitting (as seen in task manager).
My solution was to add "DoEvents" to the code.

John's code to demonstrate my close/quit mods

  Dim xlapp As Excel.Application
  Dim xlBook As Excel.Workbook, xlSheet1 As Excel.Worksheet, xlSheet2 As Excel.Worksheet
  '
  '  other code here
  '
  Set xlapp = CreateObject("excel.application")
  Set xlBook = xlapp.Workbooks.Open(filename)
  '
  ' lots of code here
  '
  xlBook.Save
  xlBook.Close
  xlapp.Workbooks.Close
  DoEvents
  xlapp.Quit
  DoEvents
  Set xlBook= Nothing
  Set xlapp = Nothing

The "DoEvents" command adds a delay so the previous command has time to complete execution. This has worked for me....