Closing Excel from Access

[beginner83]

Hi All,

On the click of a button on an Access form I create an Excel spreadsheet and write records to different tabs. This works fine if I don't touch Excel while it's running. If I close the excel sheet being created while it's running, if I try and click the button on the Access form to start the process again, it throws an error. Even when the spreadsheet successfully completes and I save and close it, if I click the button again on the Access form it won't work. I've read that this is something to do with the instance of excel that has been creating my spreadsheet isn't closed but I don't know how to close this in my code. I've created an instance of Excel..

Dim xl As New Excel.Application
Dim wkbk As Excel.Workbook
..
..
Set wkbk = xl.Workbooks.Add
With wkbk
   ..
   ..
End With

I've tried using xl.Application.Quit, that closes the instance of Excel but when I click the button on the form again it won't work and the only way to make it work is to close the database and open it again.

Does anyone know how to close Excel??? :banghead:

Thanks in advance

 

[pr2-eugin]

Try this after End With..

wkbk.Close

 

[beginner83]

Try this after End With..

wkbk.Close

I tried that and got error..

Method 'Close of object'_Workbook' failed

 

[SteveH2508]

Somewhere in your automation code for Excel you have a reference which is not fully qualified, meaning it does not start with your Excel object - then your next object and so on. This starts a new instance of Excel which hangs around afterwards.

These can be a PITA to find.

 

[pr2-eugin]

After close try Quitting the Excel application like..

xl.Quit

Is it possible to show the complete code?

 

[beginner83]

After close try Quitting the Excel application like..

xl.Quit

Is it possible to show the complete code?

When I try this I get error

Method 'Quit of object'_Application failed.

My code is quite long, I'll try and chop it down and post.

 

[GinaWhipp]

In addition to what Paul said...

You might need to close the Recordset first. (Note: change to rst to whatever you are using)...

rst.Close
Set rst = Nothing

Also, be careful *stripping down* the code because you might strip out important parts we need to troubleshoot your mesage.

 

[Rx_]

http://www.btabdevelopment.com/ts/export2ExcelNewNamedSheet
There are multiple code samples here depending on if a new worksheet or an existing worksheet is used.