Access/Excel interaction...excel.exe problem

[Ian Mac]

All,

I'm trying to run reports from Access which load an Excel.xlt and places values into a worksheet,
then creates a chart and dynamically set the SourceData.

The problem I'm getting (and this is a recurring problem for a number of people where I work) is that when trying to run the same report (sometimes even a different one) a second time the SourceData line throw up an error message.

Now here's where I get confusing, so please bare with me.

I THINK I've discovered the cause of the problem but don't know how to fix it.
at the start of the code are the lines:

Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Add("downloadProgressReport.xlt ")
Set xlSheet = xlBook.Worksheets(1)

this creates a version of Excel.
The problem when running the report a second time is that even when the first one is closed down, excel.exe is still in memory.

My thoughts on this are:

1) How can I tell Access remove the link to that excel.exe and focus on the newly created object?
I think Access is getting confused halfway through the code.
(Another question is what in the code might cause this to happen?)

or

2) How do I get rid of the other version of Excel when I close it (remove it from memory)

or

3) Check to see if Excel is currently running then use that if it is, if not CreateObject
(this may cause a problem as .xlt is not always used, sometimes the template is saved as
.xls therefore it will display an alert when it tries to load another version, unlike .xlt which will name it Report1, Report2 etc.)

or

4) I seem to remember seeing a way to loading the sheet using the CreateObject command.

Of course I may well be off track, so if anyone can help with these OR has any different ideas, I'd luv em thanks.

If I can get this licked, I will be putting on my Cape and Mask and walking right up to Mr. I.T. Person shouting, "I've come to ave the day."

Many thanks,

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-08-29 05:56

 

[dmckinney]

First thing that comes to mind...

Dim xlapp, xlBook, xlSheet As Object

is NOT the same as
Dim xlapp as Object, xlBook as Object, xlSheet as Object

Your code dims the first two as Variant and the third as Object (mightn't matter but handy to know.)

Secondly, in your place I would try to seperate your automation code into a separate function / sub with parameters, as the existing sub is too large to get your head around.

Thirdly, is there a reason why we're using late binding? i.e. why isn't there a reference created to the Excel and why are the variables not declared as Dim xlApp as Excel.Application etcetera. (It may be that there's a valid reason!) To my mind, it's preferable to use Early binding unless there's a compelling reason for doing otherwise. (Such as Excel doesn't always reside on the user's machine.)

Just some food for thought.

Regards,

Dave.

 

[Ian Mac]

On 2002-09-02 03:43, Ivan F Moala wrote:
Hi Ian
1st Code;

You need to Quit Excel 1st then Set to Nothing

eg
<pre/>
xlapp.DisplayAlerts = False
xlapp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
</pre>


2nd Code

You need to reference the range correctly

eg

<pre/>
.SetSourceData Source:=xlapp.Application.Sheets("Progress Report").Range(Range), PlotBy:=xlColumns

AND NOT

.SetSourceData Source:=Sheets("Progress Report").Range(Range), PlotBy:=xlColumns
.SetSourceData
</pre>
'
'
'
PLUS @ End of Code
'
<pre/>
xlapp.DisplayAlerts = False
xlapp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
</pre>





Note: Could Test! but try thes changes

TOP BANANA!

Trees and Woods spring to mind.
I didn't even spot that, seems to do the trick putting xlapp.Application in before the sheets part.

I hate it when it isn't a complicated affair.

As for xlapp.Quit I can't see how that would be of benefit as the Report loads up as a template and the user must save it to a location of there own choosing.

However simple setting the other objects to Nothing seems to be working.
Now all I have to do is get some monkeys to try and break the thing to make sure it's all lovely.

BIG THANKS EVERYONE, I learnt a lot from this one thread. Not least NOT to except a job fixing other people BD's

Kindest Regards,

 

[dmckinney]

Ian,

I repeat what I said about XL constants...I'm convinced that since you're using late binding, the constants recognised by Excel (xlRows etc) will not be recognised by Access under automation.

dave.

 

[Ivan F Moala]

On 2002-09-02 04:44, Ian Mac wrote:

On 2002-09-02 03:43, Ivan F Moala wrote:
Hi Ian
1st Code;

You need to Quit Excel 1st then Set to Nothing

eg
<pre/>
xlapp.DisplayAlerts = False
xlapp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
</pre>


2nd Code

You need to reference the range correctly

eg

<pre/>
.SetSourceData Source:=xlapp.Application.Sheets("Progress Report").Range(Range), PlotBy:=xlColumns

AND NOT

.SetSourceData Source:=Sheets("Progress Report").Range(Range), PlotBy:=xlColumns
.SetSourceData
</pre>
'
'
'
PLUS @ End of Code
'
<pre/>
xlapp.DisplayAlerts = False
xlapp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
</pre>





Note: Could Test! but try thes changes

TOP BANANA!

Trees and Woods spring to mind.
I didn't even spot that, seems to do the trick putting xlapp.Application in before the sheets part.

I hate it when it isn't a complicated affair.

As for xlapp.Quit I can't see how that would be of benefit as the Report loads up as a template and the user must save it to a location of there own choosing.

However simple setting the other objects to Nothing seems to be working.
Now all I have to do is get some monkeys to try and break the thing to make sure it's all lovely.

BIG THANKS EVERYONE, I learnt a lot from this one thread. Not least NOT to except a job fixing other people BD's

Kindest Regards,

Ian, Quit the running application otherwise you will be left with an instance of excel running albeit hidden.

 

[Ian Mac]

Ivan,

setting the objects to nothing and the fix in the code works fine. I quit the application in Excel and it's not running in the background.
I don't see that I have to quit from Access, I also don't see where or how I wouls trigger it,

IF Excel.Quit then

Excel.Quit?????????????

As I've said I think the link between Access is now severed, so when I quit Excel normally it doesn't sitting in memory as it has NOTHING to do with Access.

Dave,

I'm not sure what you mean?? Forgive my ignorance but my VBA isn't strong (to say the least). As mentioned, I'm a Cobbler not a programmer.

Cheers.

 

[dmckinney]

Take the example of the following line...

.SetSourceData Source:=Sheets("Progress Report").Range(Range), PlotBy:=xlColumns

If, in the immediates window (ctrl G) in the VBA editor, you type
? xlColumns ' (Print xlColumns)
You will get the response of
... 3 if you're in Excel
... and nothing if you're in Access.

When you type PlotBy:=xlColumns, in Excel it means PlotBy:=3, whereas in your automation code from Access it means PlotBy:=0. So either you redeclare the variables within access with the corresponding xl values or replace the variables with numbers.

On a more general note, you've probably noticed over the last couple of days that automation can be troublesome. In fact it's not that complicated. But it's one of those moments where messy programming will get you into trouble.

Separate all your automation code into dedicated functions, containing the minimum of code, so you can quickly see if your playing by the rules.

I hear what you say about not being a vba expert but I'm proposing that your code gets simpler - not more complex.

Keep having fun,

Dave.

 

[Ian Mac]

On 2002-09-02 05:56, dmckinney wrote:
Take the example of the following line...

.SetSourceData Source:=Sheets("Progress Report").Range(Range), PlotBy:=xlColumns

If, in the immediates window (ctrl G) in the VBA editor, you type
? xlColumns ' (Print xlColumns)
You will get the response of
... 3 if you're in Excel
... and nothing if you're in Access.

When you type PlotBy:=xlColumns, in Excel it means PlotBy:=3, whereas in your automation code from Access it means PlotBy:=0. So either you redeclare the variables within access with the corresponding xl values or replace the variables with numbers.

On a more general note, you've probably noticed over the last couple of days that automation can be troublesome. In fact it's not that complicated. But it's one of those moments where messy programming will get you into trouble.

Separate all your automation code into dedicated functions, containing the minimum of code, so you can quickly see if your playing by the rules.

I hear what you say about not being a vba expert but I'm proposing that your code gets simpler - not more complex.

Keep having fun,

Dave.

Now I think I'm with you,

Ivan's suggestion of:
.SetSourceData Source:=xlapp.Application.Sheets("Progress Report").Range(Range), PlotBy:=xlColumns

Is, I assume, letting Access know what I mean by xlColumns.
However I have tried your suggestion and it seems reasonable to me that I should be doing this and is wokring just dandy (Blimey it's all learn learn learn with this thread).

BUT, I must point out PlotBy:=3 made a real mess of my chart so I'm now using PoltBy:=2.

Thanks very much.
Any other tips you have just PM me , only kidding thanks for the help.