can't get Excel to Quit

[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

 

[JimBurke via AccessMonster.com]

I think you may still need to explicity close the workbook before quitting.
Give that a shot.

 

[TADropik]

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

 

[JimBurke via AccessMonster.com]

I think you may need to EXPLICITLY close the workbook object, i.e.
objWorkbook.Close.

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

I think you may still need to explicity close the workbook before quitting.
Give that a shot.

[quoted text clipped - 34 lines]

 

[JimBurke via AccessMonster.com]

See this link for more info:

http://www.eggheadcafe.com/conversation.aspx?messageid=31915362&threadid=31915360


Looks like you may also need to set the objects to nothing as well before
quitting.

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

I think you may still need to explicity close the workbook before quitting.
Give that a shot.

[quoted text clipped - 34 lines]

 

[Stewart Berman]

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.

 

[EricG]

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