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.