access wont let go :(

[Gillian g]

I have an Access2000 app where i use automation to create
a new excel document from a template. using the following
code:
*--------
Set xlApp = CreateObject("Excel.Application")
Set xlWorkBook = Excel.Workbooks.Add(sTemplate)
Set xlWorkSheet = xlWorkBook.Sheets(1)
*--------

then i do some processing to dump values into the sheet.
then i save and exit using this code

*--------
Call xlWorkBook.SaveAs(sExportFile)
xlWorkBook.Close
xlApp.Quit
Set xlWorkSheet = Nothing
Set xlWorkBook = Nothing
Set xlApp = Nothing
*--------

the problem is that excel2000 never quits. it disappears
but is still visible in the detailed process list. Unless
I actually quit access it wont go away. even if i leave
the instance visible after processing and then close it
manually it wont completely close until i close access.

for reference i am calling this from a function in a
standard module with all local variables (to the
function). the cleanup code is always called whether
there is an error or not.

been frustrated by this one for a while so any guesses
would help. TIA

cheers

 

[Peter Huang [MSFT]]

Hi Gillian,

It seems that you have not release all the object referenced in your Macro.
You may try the code below to see if the problem persists. If yes, please
try to try to use Late-binding.
You may try to delcare the variable as Object.
e.g.
change the delcaration from
Dim app As Excel.Application
to
Dim app As Object


Option Compare Database

Sub test()
Dim app As Excel.Application
Dim books As Excel.Workbooks
Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet
Set app = CreateObject("Excel.Application")
app.Visible = True
Set books = app.Workbooks
Set book = app.Workbooks.Add
Set sheet = book.Sheets(1)
book.SaveAs ("C:\tet.xls")
book.Close False
Set book = Nothing
Set books = Nothing
app.Quit
Set app = Nothing
End Sub

Here is a helpful KB link.
http://support.microsoft.com/?id=319832



Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------

 

[GG]

Hazah!

i switched to late binding and did not take care of the
problem. But i found that if i pulled my template (xlt)
from and saved to a local drive it WOULD work when
stepping through the code. it WOULD NOT work when running
the access database in user mode.

BUT then i put an 'End' at the end of my cleanup block as
below and it worked! and it doesn't close the database
either, like i thought it might!

Cleanup:
On Error Resume Next
xlWorkBook.Close False
Set xlWorkSheet = Nothing
Set xlWorkBook = Nothing
xlApp.Quit
Set xlApp = Nothing

DoCmd.Hourglass False
ExportSiteSummary = True
End '*********** this worked **********
Exit Function

thanks for the help, if anyone could explain this better
to my and WHY this worked i would appreciate it. there all
references in the function were local vars so i dont know
why excel it would hang on like that...

cheers
gg

 

[Peter Huang [MSFT]]

Hi Freshg,

I want to know if you have tried my code in both early binding and later
binding. My original code is early binding. to midified it to late binding,
you may need to
release reference to the Microsoft Excel 10.0 Object Library. You may
achieve that by navigating to Tools/Reference and unchecking the Microsoft
Excel 10.0 Object Library.

Then you can modify my code as follows.
Sub test()
Dim app As Object
Dim books As Object
Dim book As Object
Dim sheet As Object
Set app = CreateObject("Excel.Application")
app.Visible = True
Set books = app.Workbooks
Set book = app.Workbooks.Add
Set sheet = book.Sheets(1)
book.SaveAs ("C:\tet.xls")
book.Close False
Set book = Nothing
Set books = Nothing
app.Quit
Set app = Nothing
End Sub

Please perform the test and let me if this works for you.
BTW have you taken a look at the KB link.
http://support.microsoft.com/?id=319832

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

 

[gg]

Peter,
Once I removed the hard reference it worked perfectly!
THats good because just after my last post I started
seeing the symptoms of calling 'end'. not good.

thank you very much for your help.
g

 

[Peter Huang [MSFT]]

Hi Freshg,

That's cool. Cheers!

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------

Content-Class: urn:content-classes:message
From: "gg" <[email protected]>
Sender: "gg" <[email protected]>
References: <[email protected]>

<[email protected]>