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
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