Excel instance doesnt quit

[easyit]

Hi,

I have a function that creates a excelsheet, works OK but it doesn't close excel afterwards. I'm doing something not rigth, but even after reading several posts (...about Having incorrect or non-fully qualified references etc) I have not figured it out. Is ther someone who can tell me what it is that i'm doing wrong here?

Public Function CreateExcelWorkbook(ByVal strWorkbookName As String) As Boolean
Dim appExcel As Excel.Application
Dim wbkWorkbook As Excel.Workbook
Dim wksWorkSheet As Excel.Worksheet

On Error GoTo CreateExcelError
CreateExcelWorkbook = True
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = True 'False
Set wbkWorkbook = appExcel.Workbooks.Add
Set wksWorkSheet = wbkWorkbook.Worksheets.Add
wksWorkSheet.SaveAs FileName:=strWorkbookName

CreateExcelExit:
With appExcel
Workbooks(wbkWorkbook.name).Close
appExcel.Application.Quit
End With
Set wksWorkSheet = Nothing
Set wbkWorkbook = Nothing
Set appExcel = Nothing
Exit Function

CreateExcelError:
CreateExcelWorkbook = False
MsgBox Err.Description

GoTo CreateExcelExit

End Function

Maarten

 

[ByteMyzer]

Change the line:

appExcel.Application.Quit

...to:

appExcel.Quit

...and see if that helps.

 

[VBslammer]

I've never tried to save the workbook using the worksheet object - usually the Excel object or the Workbook object.

Public Function CreateExcelWorkbook(ByVal strWorkbookName As String) As Boolean
  Dim appExcel As Excel.Application
  Dim wbkWorkbook As Excel.Workbook
  Dim wksWorkSheet As Excel.Worksheet
  
  On Error GoTo CreateExcelError

  Set appExcel = CreateObject("Excel.Application")
  Set wbkWorkbook = appExcel.Workbooks.Add
  
  Set wksWorkSheet = wbkWorkbook.Sheets.Add
  wksWorkSheet.[blue]Name[/blue] = "MyAddedSheet"
  
  [red]wbkWorkbook.SaveAs[/red] strWorkbookName
  
  appExcel.Application.Visible = True
  CreateExcelWorkbook = True
  
CreateExcelExit:
  On Error Resume Next
  appExcel.Quit
  Set wksWorkSheet = Nothing
  Set wbkWorkbook = Nothing
  Set appExcel = Nothing
  Exit Function
  
CreateExcelError:
  MsgBox Err.Description
  [blue]Resume[/blue] CreateExcelExit   [green]'clears error[/green]
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[easyit]

Thanks for your responses.

ByteMyzer,

Sorry already tried that - makes no difference.

VBslammer,

Makes no difference either, the results are the same I have e new xls and excel instance that won't close.

it seems to me that from the access VBA it is not allowd to close the app.

 

[RoyVidar]

I would try altering a little on the order of the release statements.

[tt]Set wksWorkSheet = Nothing
Set wbkWorkbook = Nothing
appExcel.Quit
Set appExcel = Nothing[/tt]

If this doesn't help, perhaps a DoEvents or two? One after the saveas command, and one after appExcel.Quit. And before you try, kill any extra instances of Excel through the Task Manager.

Roy-Vidar

 

[easyit]

Roy,

Thank you but...no success. I've seen your name on several posts regarding the same or similar items, do you perhaps know anything else I could try? I am considering using api and killing the process, but only as a last resort.

Maarten

 

[ByteMyzer]

Take a close look at your code. The following lines should be changed, from:

With appExcel
Workbooks(wbkWorkbook.name).Close
appExcel.Application.Quit
End With

...to:

With appExcel
    .Workbooks(wbkWorkbook.name).Close
    .Quit
End With

Also, try the following substitute Dim statements:

  Dim appExcel As Object
  Dim wbkWorkbook As Object
  Dim wksWorkSheet As Object

 

[RoyVidar]

I think the combined suggestions of ByteMyzer, VBSlammer and myself pretty much sums up most of the "gotchas" I know about, except closing the workbook, which need to be prior to the quit statement and releasing the object variables.

You've probably also seen some/most of the links on the topic, I guess.

A memory leak, getting an extra instance of Excel in memory, indicates there is a problem somewhere. Using the End statement or an API will probably only cure the symptom, not the cause. Not finding the culprit, may come back and bite you...

I was about to suggest trying late binding too, but ByteMyzer beat me to it;-) But I wouldn't use With blocks, cause that's also one possible culprit... XL2000: Automation Doesn't Release Excel Object from Memory.

You are entirely sure this is the only routine referencing Excel? You don't have anymore lines at all refering to Excel, no ActiveCell, ActiveSheet, Cells, Range...? The posted code is all?

Roy-Vidar

 

[easyit]

Roy,


The posted code is all there is. I already have tried declaring them as an object, no succes. Have you (or somebody else) tried this code to see how it works out on your PC? I'm using XP and office 2003.

Maarten

 

[PHV]

Why not simply this ?
Public Function CreateExcelWorkbook(ByVal strWorkbookName As String) As Boolean
Dim appExcel As Excel.Application
Dim wbkWorkbook As Excel.Workbook
On Error GoTo CreateExcelError
CreateExcelWorkbook = True
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True 'False
Set wbkWorkbook = appExcel.Workbooks.Add
wbkWorkbook.SaveAs FileName:=strWorkbookName
CreateExcelExit:
wbkWorkbook.Close
Set wbkWorkbook = Nothing
appExcel.Quit
Set appExcel = Nothing
Exit Function
CreateExcelError:
CreateExcelWorkbook = False
MsgBox Err.Description
Resume CreateExcelExit
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[easyit]

...still leaves excel instance running in the back. I think i all solutions are pretty straight forward, but we're overlooking something. I'm giving it rest for a couple of days. Thanks anyway!

 

[RoyVidar]

Initial function leaves an instance of Excel in memory.

VBSlammers suggestion, with or without later suggested alterations does not (both on my a2k and a2k2 setups).

Try creating a new database, only paste this function, and see if the problem arises then too.

Roy-Vidar

 

[easyit]

The problem also arise with the suggestions mentioned in a fresh new db.

 

[easyit]

...took another pc, pasted the code and voila! No problem whatsoever. I'll test the app on the target pc(s), but probably it will work. Don't know why it doesnt work on my regular pc if anybody has a clue please let me know.

Thank you all for your input, again tek-tip has helped me!

Maarten

 

[Ruppie]

Hi,
I was having simliar random issue on whether excel closed on not but not since finally getting the order right as below. The only difference I see from previous suggestions is to set the workbook saved as true.

xlBook.SaveAs ("C:\working\file_" & Format(Date, "mmdd") & ".xls")
xlBook.Saved = True
Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

Cheers Rupert

 

[easyit]

Rupert,

Thanks, but it doenst make a difference. I beleive that there is a problem specific wiht my PC, have to do some tests.

 

[easyit]

...Made a fresh install of office today and the problem was gone. Really don't know wwhat was the cause of the faulty installation, but I can move on and close this subject. Thanks again everybody!