OpenQuery and TransferSpreadsheet?

[Gasman]

Hi all,
Trying to help a member on another forum, where he is complaining about slowness for creating 10 sheets i an excel file for over 300 customers.
First he is opening the query then TransferSpreadsheet?

I do not know if the Transfer takes into account that the query supporting it is already open, or opens a new copy?

Is anyone able to advise to that please?

 

[CJ_London]

I open an excel object, use copyfromrecordset, use excel automation to format the worksheet rows and columns and add functionality such as filtering. Repeats for 3 further worksheets then saves. Largest worksheet has around 5000 rows.

Takes less than 10 seconds from pressing the button to saving the file

 

[June7]

Why are they opening query?
How many records for 300 customers?

Does Transfer pull from open object? I don't think so.

 

[Gasman]

Why are they opening query?
How many records for 300 customers?

Does Transfer pull from open object? I don't think so.

No idea TBH. I have told them that the Transfer will do that, and then mentioned the above subject, but said I am not sure about it, which is why I asked here.

 

[CJ_London]

First he is opening the query then TransferSpreadsheet

No need to open the query, transferspreadsheet does that anyway

Not clear if the requirement is for 300 x 10 sheet workbooks or one workbook with 10 sheets x 300 rows. If the former my method would probably take around 10 minutes, the latter around 15 seconds

 

[Gasman]

The former.

 

[CJ_London]

Ok - so how long is

complaining about slowness

More than 10mins to produce 300 workboooks?

 

[Gasman]

Initially, it currently takes approx 1 minute per file to generate. Very Acceptable!!

Unfortunately, after around 500'ish files are generated, the process tends to start lagging. Taking 2,3,4 sometimes 5 minutes a file to generate. Before I just replace the workstation with a possibly faster processor, and try that, I am wondering if the current commands I am using are still "up to date" or if there is a better / more efficient way to do this.

I am basically using DoCmd.OpenQuery and DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8 commands to accomplish this.

I do have sleep commands between each OpenQuery command. So after running the Customer Address query, capturing that data and exporting to the template, Sleep (2000), and then start running the Customer Purchases query for example. I believe I added this due to getting a customer id template.xls file already in use type message in the past.

 

[CJ_London]

All slow methods relatively, plus Excel8? A 2 second Sleep?

Slowing down after 500 odd reports? Might be due to poor coding, old vba version. hardware issues. Wouldn’t like to speculate beyond the addition of the sleep simply masks the issue

 

[Minty]

I've experienced a slow down with repeated calls to Excel, despite lots of efforts to force it to "tidy up"
I can't honestly say I've tried to create 300 workbooks in one hit though.

I do produce time sheets for up to about 100 staff in one app, and that gradually slows down to a snails pace if you try and do more than about 50 of them in one go.

There is a definite improvement in performance with a freshly rebooted PC, so there is obviously resources not being freed up somewhere. I've had similar experiences with PowerPoint file creation and manipulation.

 

[Gasman]

Thank you both.

 

[Pat Hartman]

I've created about 30 spreadsheets in one export to produce a separate commission list for each sales person. Each export had about 40 rows. It probably took a minute or so to run.

I'm going to guess that 300 could be slow using TransferSpreadsheet since each time you run the TransferSpreadsheet, you are opening an instance of Excel. That is what is talking the time - opening and closing Excel. For 300, I might try opening excel then use a loop that creates a new workbook, runs the copyfromrecordset and closes the workbook. This should take less time for each workbook than also opening/closing excel. Please let us know the result.