Kill Excel!

[KellyK]

Hi all, I'm struggling on Excel automation. I'm populating an Excel spreadsheet through VBA code in Access. I use the following code:

Dim xlApp As Object
    ...
Set xlApp = New Excel.Application
    ...
xlApp.Quit

Set xlApp = Nothing

Yet the Excel process is still running and continues to run until I exit my Access database. If I try to open a spreadsheet with the database open, the sheet won't open correctly. I don't want to have to quit my Access app in order to check the spreadsheet, because after the spreadsheets are compiled I email them out to people. I just like to look them over first to make sure they're accurate. My question is: is there a way to kill all Excel processes BEFORE exiting Access?

Thanks in advance.
Kelly

 

[kevinclark]

Kelly what if you try using

Set xlApp = CreateObject("Excel.Application&quot

as opposed to

Set xlApp = New Excel.Application

 

[KellyK]

Thank you for the suggestion, Kevin, but there was still an instance of Excel running after trying that.

back to the drawing board

 

[kevinclark]

One more shot at it .... how about just using

Dim xlApp As Excel.Application

 

[KellyK]

No dice.

Thanks though!

 

[Guest_imported]

Make sure you do and xlapp.quit and "set xlapp = nothing".

 

[MarrowGenx]

Kelly,

I had similar problems and here are some solutions that I found:

1. Make sure you utilize xlapp.quit and set xlapp = nothing.
2. Try to get rid of excel objects in the reverse order in which you created them (i.e. set = nothing).
3. Try not to reset any excel objects. See example:

Set xlrange = XX
<Random code>
Set xlrange = YY
<Random code>
Set xlrange = ZZ

4. Try to rewrite your code bits a pieces at a time and test it along the way. This can help you find what part of the code is causing excel to hang.

The above items are just my experience and may or may not be helpful to you.

I just finished rewriting a large portion of my code one bit at a time to find what was causing excel to hang. I started with the open and closing of excel to make sure that process was working, then slowly added parts in to locate the problem. I found item #3 above was the cause of my frustrations.

 

[KellyK]

Thank you MarrowGenx, your post is very insightful. Most likely #3 is the cause of my woes as well. The problem is that I need to send out 100+ Excel files, so for each record in my recordset, I reset the workbook and worksheet in order to save the file under the corresponding salesperson's name. I guess I will need to find a way to do this without resetting those Excel objects...

 

[baltman]

Just a shot in the dark, but if your Excel is &quot;visible=.f.&quot; and you didn't turn off the alerts (e.g. &quot;do you want to quit without saving&quot, you will hang.

Try &quot;.DisplayAlerts = .f.&quot; at the start of your automation code.

Brian

 

[KellyK]

Thanks, Brian!