export to multi-excel files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a monthly sales database w/~100 salespersons.
I like to export to 100 spreadsheets, ie. sales01.xls, sales02.xls...
I can do one by one for 100 times.
Is this possible just doing one time then get 100 spreadsheets?
and How?

Thanks
 
You can do this in VBA with a For Next Loop.
Two issues.
If the actual names are sales01.xls. sales02.xls, you will have a small
problem when you hit 100. I would suggest sales001.xls,
sales002.xls...sales100.xls

Is there a different query for each spreadsheet or is it one parameter
query? The latter is the better choice, but it can be done either way.

Here is the basic looping logic, but it does not address the FileName
argument of the TransferSpreadsheet.

Dim strPath As String
Dim strFileName As String
Dim lngCtr As Long

strPath = "\\Myserver\MyFolder\"

For lngCtr = 1 to 100
strFileName = strPath & "sales" & Format(lngCtr, "000") & ".xls"
DoCmd.TransferSpreadsheet, acExport, , "QueryName", strFileName, True
Next lngCtr

If you need help with creating the correct Query reference, post back.
 
Thanks, I will try.


Klatuu said:
You can do this in VBA with a For Next Loop.
Two issues.
If the actual names are sales01.xls. sales02.xls, you will have a small
problem when you hit 100. I would suggest sales001.xls,
sales002.xls...sales100.xls

Is there a different query for each spreadsheet or is it one parameter
query? The latter is the better choice, but it can be done either way.

Here is the basic looping logic, but it does not address the FileName
argument of the TransferSpreadsheet.

Dim strPath As String
Dim strFileName As String
Dim lngCtr As Long

strPath = "\\Myserver\MyFolder\"

For lngCtr = 1 to 100
strFileName = strPath & "sales" & Format(lngCtr, "000") & ".xls"
DoCmd.TransferSpreadsheet, acExport, , "QueryName", strFileName, True
Next lngCtr

If you need help with creating the correct Query reference, post back.
Click to expand...
 
Back
Top