excel just will not close

[hardyd44]

Hi,

i am running a script from a button is pressed to import 36,000 records from an excel spreadsheet - that works fine, however I can not get excel to close completly when finished - i have searched the forum and the internet I havetried different bits of code but none seem to work,open taskmanager and it is still there. I have tried it on 2 differnt PC's to no avail can anyone suggest a sure fire way of getting excel closed - here is the code:

Private Sub Update_Holidays_Click()
On Error GoTo Err_Update_Holidays_Click

' Declare object variables.
Dim appXl As Excel.Application
Dim wrkFile As Workbooks
Dim wrkSheet As Worksheets
' Set object variables.
Set appXl = New Excel.Application
Set wrkFile = appXl.Workbooks
' opens workbook
wrkFile.Open "c:\DriverControl\Holiday2006-2007.xls"

appXl.Visible = False
' deletes existing imported holidays table
DoCmd.DeleteObject acTable, "Imported Holidays"
' deletes access_feed table then transfers spreadsheet to new access feed table
DoCmd.RunMacro "import2"
' filters unused records and creates new imported holidays table
DoCmd.OpenQuery "Imported Holidays Builder"

wrkSheet.Close
wrkFile.Close
appXl.Quit

' Close the object references.
Set wrkSheet = Nothing
Set wrkFile = Nothing
Set appXl = Nothing


Err_Update_Holidays_Click:
' MsgBox Err.Description
' Resume Exit_Update_Holidays_Click
MsgBox "Import Complete"

End Sub

this really is causing me major problems as the imported data is reworked in access and exported to another excel spreadsheet which then falls over with an out of memory error. I can't move the whole lot to access as the excel spreadsheets are in daily use and have to be used - I have spent weeks on this trying various methods and nothing seems to work.

thanks Dean

 

[Newman]

I've seen somewhere that you have to use the «with» function to make it work.

With appXl
    .visible
    [...]
    .quit
End with

I don't remember the exact reason, but I'll look for it and send it to you later on.

 

[boblarson]

Just a thought, but when I've had that problem it's because I'm opening two instances of Excel, but only closing one. Could this be the case due to something in this macro:

DoCmd.RunMacro "import2"

I know probably not, but I thought I'd mention it.

 

[hardyd44]

done it !!!!!

boblarson - you are my hero !!!!

that was the problem - I re wrote the macro in VB and put it in the script instead of running import2 - the import worked fine (and a lot quicker !) but excel was still open afterwards :-( , so I then removed the lines to open and close excel before and after the various import commands and viola!

The import takes a couple of seconds and no instance of excel in the task manager - so what was happening is I was opening excel then opening it again during the import and only closing one instance at the end the script

I am a very happy man as I have been struggling with this one, off and on for weeks.

Happy New Year to everyone !!!!!

 

[boblarson]

Glad to be of service! Happy New Year to you too.