Excel automation from Access - Excel instance won't close

[Andrew]

Hi all,

I wonder if any of you kind folks can shed any light on this.

I have a form in Access designed to get data from an Excel workbook.

Along the way, various bits of processing are done, such as allowing
the user to select a specific worksheet in the workbook, browse for
another file to import, navigate up and down rows within the worksheet
to specify the starting row for the import, clearing out unwanted
columns etc.

When the work is done, I have code designed to close the file, quit
Excel and destroy any object variables used. But although the code
works perfectly, I notice that if I go to the Task Manager after
importing data, Excel is still running.... I am using Vista, so am
wondering if this is some form of security issue with Vista not
allowing one application's code to close another application, but I
suspect it's just something wrong with my code...

Can anyone help?

Here's the code to instantiate Excel, and the code to close it again.
The cleanup code is definitely being run, and doesn't appear to
generate any errors (I've stepped through it, keeping an eye on the
err.number property).

Thanks a lot

Andrew

--General declarations--
Private xlApp As Excel.Application
Private wb As Excel.Workbook
Private ws As Excel.Worksheet
Private rng As Excel.Range

--Sub to set up the connection--

Sub ConnectToExcelFile()
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then 'Excel not running - start it
Set xlApp = CreateObject("Excel.Application")
Err.Clear
End If

Set wb = xlApp.Workbooks.Open("My file path and file name.xls")
<snipped code>
End Sub


--Sub to close the connection --

Sub CloseExcelConnection()
On Error Resume Next
wb.Close False
xlApp.Quit
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

End Sub

 

[RoyVidar]

Hi all,

I wonder if any of you kind folks can shed any light on this.

I have a form in Access designed to get data from an Excel workbook.

Along the way, various bits of processing are done, such as allowing
the user to select a specific worksheet in the workbook, browse for
another file to import, navigate up and down rows within the worksheet
to specify the starting row for the import, clearing out unwanted
columns etc.

When the work is done, I have code designed to close the file, quit
Excel and destroy any object variables used. But although the code
works perfectly, I notice that if I go to the Task Manager after
importing data, Excel is still running.... I am using Vista, so am
wondering if this is some form of security issue with Vista not
allowing one application's code to close another application, but I
suspect it's just something wrong with my code...

Can anyone help?

Here's the code to instantiate Excel, and the code to close it again.
The cleanup code is definitely being run, and doesn't appear to
generate any errors (I've stepped through it, keeping an eye on the
err.number property).

Thanks a lot

Andrew

--General declarations--
Private xlApp As Excel.Application
Private wb As Excel.Workbook
Private ws As Excel.Worksheet
Private rng As Excel.Range

--Sub to set up the connection--

Sub ConnectToExcelFile()
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then 'Excel not running - start it
Set xlApp = CreateObject("Excel.Application")
Err.Clear
End If

Set wb = xlApp.Workbooks.Open("My file path and file name.xls")
<snipped code>
End Sub


--Sub to close the connection --

Sub CloseExcelConnection()
On Error Resume Next
wb.Close False
xlApp.Quit
Set rng = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

End Sub

As far as I can see, it isn't the posted code that causes such error,
that is, unless there's something wrong other than the code.

Dunno, you could perhaps try a DoEvents after closing the workbook,
but I doubt it will matter.

If there are coding errors, I'm guessing it is in the snipped part,
and what is usually causing such, is when using Excel objects without
"anchoring" them to their respective parent objects.

See for instance
http://support.microsoft.com/default.aspx?kbid=178510
or post back with more details.

 

[Andrew]

As far as I can see, it isn't the posted code that causes such error,
that is, unless there's something wrong other than the code.

Dunno, you could perhaps try a DoEvents after closing the workbook,
but I doubt it will matter.

If there are coding errors, I'm guessing it is in the snipped part,
and what is usually causing such, is when using Excel objects without
"anchoring" them to their respective parent objects.

See for instancehttp://support.microsoft.com/default.aspx?kbid=178510
or post back with more details.

Excellent. I'll have a look later, but I think the article you linked
describes my code pretty well!

Thanks for your help

Andrew