Closing Excel - is Windows API really necessary?

  • Thread starter Thread starter RzB
  • Start date Start date
R

RzB

I have the following , very simple code to create/close
an excel spreadsheet. After executing this code I was
expecting to be able to open the spreadsheet manually.
However I can only do this if I first close the Access
db that this code is running in. I have spent all day
reading and re reading posts and KB Articles but still
can't get it to work. I have seen one post that indicates
using a Win API call as a last resort.

I am using Win XP Pro and Access/Excel 2002.

Is my code ok? Or am I doing summat wrong?

Dim objXlApp As Excel.Application
Dim objXlWkb As Excel.Workbook
Dim objXlSht As Excel.Worksheet

Set objXlApp = New Excel.Application
Set objXlWkb = objXlApp.Workbooks.Add
Set objXlSht = objXlWkb.Worksheets("Sheet1")

objXlSht.Range("A1") = "HelloWorld"

objXlWkb.SaveAs ("C:\Maplspreadsheets\RzB.xls")

objXlWkb.Close
objXlApp.Quit

Set objXlSht = Nothing
Set objXlWkb = Nothing
Set objXlApp = Nothing

Thanks,
Roy
 
My experience is that you need to set to Nothing the objects that are
"within" EXCEL, such as workbooks and worksheets and ranges and such, before
you quit the application. Try this:

Dim objXlApp As Excel.Application
Dim objXlWkb As Excel.Workbook
Dim objXlSht As Excel.Worksheet

Set objXlApp = New Excel.Application
Set objXlWkb = objXlApp.Workbooks.Add
Set objXlSht = objXlWkb.Worksheets("Sheet1")

objXlSht.Range("A1") = "HelloWorld"

objXlWkb.SaveAs ("C:\Maplspreadsheets\RzB.xls")

Set objXlSht = Nothing
objXlWkb.Close
Set objXlWkb = Nothing
objXlApp.Quit
Set objXlApp = Nothing
 
Back
Top