Automating excel from MAccess 2007

[Ingman]

Hi all,

I want to open and read some information from an excel worksheet from
Microsoft Access 2007. Im using early bindings and the code looks something
like this.

....
Dim xlApp as Excel.application
Dim xlWb as Excel.workbook
Dim xlWs as Excel.worksheet


Set xlapp = New Excel.application

Set xlWB = xlApp.Workbooks.Open(strFilePath)

Set xlWs = xlWb.ActiveSheet

.......

xlWb.Close
xlApp.Quit

Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

The problem is that the Excel process continues to live on in processes, ive
read a lot about this problem and found no "automatic" solution to it. xlApp
is not = nothing, and so process continues to live on. Before i tell my
customers that they have to manually go in and kill the process threw the
task manager i just wanted to check if anyone has a solution to this problem?
Any ideas would be greatly appreciated!

//Ingman

 

[Alex Dybenko]

Hi,
try to comment parts of your code between posted parts, to find out which
line cause this. also look at similar tread here with subject "Excel
instance remains open", there OP found that line Destination:=Range("A1")
caused this problem

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Ingman]

Hi again,

In my test project im not using anymore code then described above. If I only
use the code below, the instance closes down properly:

Dim xlApp = Excel.Application

Set xlApp = New Excel.Application

xlApp.Quit

Set xlApp = nothing

if i use a workbookreference of any kind the process goes on after quit. So
xlApp.Workbooks.Open doesnt work. Is it something fundamental ive missed??

 

[Ingman]

Hi again,

Tried it with same outcome, except that now it will always be just one
unterminated process which is better I guess. A quick question what will
happen if theres an open process which the user is currently working
with(visible and all) ? 2nd do i have to change to late bindings, or can I do
something similar with early bindings? The reason for this is that i read
somewhere that early bindings are more stable and has overall better
performance.

 

[merwcat]

Hi I have run into this problem in the past and the most successful solution
I found was to adapt an Access class library from Gary Robinsons VB123
Toolbox site as the basis for XL automation, its not free but the cost is
minimal and it adapts very easily for all sorts of purposes it never seems to
leave any open processes running except where I do something silly with it. I
use it to produce a lot of Excel charts and reports from access. May not be
the answer you want but it was the solution for me.

 

[Alex Dybenko]

Hi,
advantage of late binding - that it works with any excel version.
Performance for both bindings is the same

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[OssieMac]

Try setting each variable to nothing after each is closed as per the
following example. I have had some success with this but have not tested
exhaustively so not really sure if it works in all cases but so far it seems
to be working with my application. I will be interested in what happens with
yours. I adapted it from something unrelated that I found on this forum and
thought it worth a try.

Set xlWs = xlWb.ActiveSheet
Set xlWs = Nothing

xlWb.Close
Set xlWb = Nothing

xlApp.Quit
Set xlApp = Nothing

 

[Ingman]

Hi all,

And thx for all ideas. Ossie, ive tried your example unfortunatly without
success .... However the solution Alex Dybenko suggested(limiting the
unterminated process to only 1) is ok for this project. I can live with one
unterminated process, just changed to late bindings, thx for the info about
the performance! Also gone check out the 3rd party component recommended for
future projects!

Thank you again for the feedback!

 

[Ingman]

Hi again,

Guess I was a little to quick accepting the solution. Consider the following
case, the user has an excel application open before using the access
form(observe only one excel instance is now running). This in itself isnt a
problem, however if the user closes the excel appliction while the code is
running from the access form, an exception in access arises "Object
required", the reason is ofc that the proccess has been terminated by the
user. Are there anyway around this, check after GetObject if the applicaiton
is visible or something like that. Alex do you know in which order your
fIsAppRunning gets the excel process(last/first created etc), could it be
possible to loop threw availaible process until for example a process where
the application isnt visible is recieved?