can't get Excel to Quit

  • Thread starter Thread starter TADropik
  • Start date Start date
T

TADropik

I use the code below to create an Excel workbook and save it.
However, the OS doesn't let go of Excel. I still see EXCEL in the Task
Manager.


Dim objExcel As excel.Application
Dim objWorkbook As excel.Workbook
Dim objWorksheet As excel.Worksheet

Set objExcel = GetObject("", "excel.application")

objExcel.Visible = False

Set objWorkbook = objExcel.Workbooks.Add
objWorkbook.Worksheets.Add
Before:=objWorkbook.Worksheets(objWorkbook.Worksheets.Count)
objWorkbook.ActiveSheet.Name = "Data-Test"
Set objWorksheet = objWorkbook.Worksheets("Data-Test")
objWorksheet.Select

objExcel.ScreenUpdating = False

objWorksheet.Cells(1, 1) = "TESTING EXCEL"
objWorksheet.Cells(1, 1).ColumnWidth = 17
objWorksheet.Cells(1, 1).Font.Bold = True

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs ("C:\Excel\Test_Excel.xls")

objExcel.DisplayAlerts = True
objExcel.ScreenUpdating = True
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
 
What I did still didn't work.

I inserted the following line:
objExcel.ActiveWorkbook.Close

See code below:

Dim objExcel As excel.Application
Dim objWorkbook As excel.Workbook
Dim objWorksheet As excel.Worksheet

Set objExcel = GetObject("", "excel.application")

objExcel.Visible = False

Set objWorkbook = objExcel.Workbooks.Add
objWorkbook.Worksheets.Add
Before:=objWorkbook.Worksheets(objWorkbook.Worksheets.Count)
objWorkbook.ActiveSheet.Name = "Data-Test"
Set objWorksheet = objWorkbook.Worksheets("Data-Test")
objWorksheet.Select

objExcel.ScreenUpdating = False

objWorksheet.Cells(1, 1) = "TESTING EXCEL"
objWorksheet.Cells(1, 1).ColumnWidth = 17
objWorksheet.Cells(1, 1).Font.Bold = True

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs ("C:\Excel\Test_Excel.xls")
objExcel.ActiveWorkbook.Close

objExcel.DisplayAlerts = True
objExcel.ScreenUpdating = True
objExcel.Quit

Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
 
This is systematic of the failure to destroy all objects before attempting to exit. In MS Access
the Access window doesn't close after the database is closed and clicking on the close button has no
effect. Excel hides but doesn't close.

Set all objects to nothing before they go out of scope. Failure to do so results in ghosts.
 
I copied this code into a blank module of a blank database and ran it. I had
to change the path for the Excel file to somewhere I had proper permissions,
and the code worked. Perhaps you are getting an error when trying to create
or save the file because of permissions? The "DisplayAlerts" might be
masking a problem - trying leaving alerts on to test your code. When it
works, you can turn it back off.

HTH,

Eric
 
Back
Top