Access/Excel Automation Slowdown

[rnegde]

I have a Access db that I use a lot of automation on. The form queries data, opens an Excel form, saves as one of the fields data, and populates some of the cells. Then I edit other cells and print. At that point I close Excel, hit another button on the form which opens Word and does the same. After that I clear the queries data and start all over again. This process is done anywhere from 20 to 60 times a day.
I have noticed that as more time goes by it seems that the opening and saving part is getting slower and slower. It seems more noticeable on the Excel sheet than the doc. Does anyone have any ideas?
I've looked to see if there were temps or some cache that needed cleaning , but I can't find anything. So far, I haven't waded far enough through MSKB to find anything either.

If anyone can point me in the right direction of what to check or look for, I would appreciate it!

 

[cheerio]

Hi

There are two different possibilities that come to mind.

First, when you close the Excel workbook are you absolutely sure that you are also closing Excel. If not, you could be opening multiple instances of Excel. These would not necessarily be visible on the windows toolbar if opened from code. A quick check - after closing the workbook do CTRL+ALT+DELETE and observe what applications are running.

Second, the relevant object variables need to be explicitely set to Nothing in order to release the memory they were using. For example:

Dim xyz As Excel.Workbook
... do things
xyz.Close ' close workbook
Set xyz = Nothing ' release memory

If you have other objects such as worksheets then set them to nothing as well.

 

[rnegde]

Hi Cheerio,
As to the first part, I knew I was shutting it down everytime. That was one of the first things I learned about automation.
And I was pretty sure I had the ob var set to nothing, but I did go and double check the code and yes, it is set to nothing.
Good ideas, I wish one of them was the problem, that way it would be fixed now!!
I even thought that maybe it was the large number of files in the directory that it saves to, but I moved them all and then ran it a few times and it wasn't any faster.
Oh well, back to the drawing board....

Thanks for the ideas though...

 

[cheerio]

If no one else here comes up with some alternative ideas I think you need to isolate the problem. These are some tests I would try:

1 Does the problem clear up if I close down and retart the Access front end or do I have to restart Windows

2 Does the problem occur if I only do the Excel processing and comment out the Word and vice versa

3 What happens if the Excel file is kept open and you keep on updating the same copy

4 Ditto for Word

The idea is to try to identify the code that causes the problem. If you can reproduce the problem by repeatedly running a small piece of code then you can post that here for more specific advice.

 

[smurf01]

Hi, I have had this problem myself only yesterday with an excel spreadsheet containing a great many Active X controls.
The problem seems to be that when you open up the sheet excel goes away to see if the controls are already there, whilst this is going on nothing happens. Microsoft acknowledge this as a problem. I am running Windows 2000 Pro and by following the path below i found a temp folder with 3,546 files in it.

My Computer/c:/Documents & Settings/Your User Name/Local Settings/Temp

In Microsoft KB they have a workround that you can try

this is the article number XL2000: File Opens Very Slowly (Q299372)

 

[khood]

rnegde,

I had a similar problem with Word mail-merge. I was opening a new document based on a template, merging query results, setting the mail-merge data source, printing x number of letters, and then closing Word. No matter how many documents were merged, it ran like a dog. I was also cleaning up properly after every routine.

After unsuccessfully trying to make the performance more acceptable, I finally switched to using TRANSFERTEXT to a flat file, then perform the mail merge based on the file (still all automated from Access). What a difference! It runs in a fraction of the time.

I realize that you are not doing exactly the same thing, but I thought this may give you some ideas to try. My performance hit was in setting up the data source for Word using automation. Now, I setup the Word templates with a standard datasource in advance, then create this file new for every merge.

One more thing, I echo what Cheerio is saying. Do this check - after your routines run perhaps a dozens times, check to see what tasks are running (CTRL-ALT-DLT). Make sure that no additional instances of Access, Excel or Word are running that aren't necessarily visible on your taskbar.

Good luck.
K.Hood