Exporting multiple files

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

Guest

I am using the following code to export data for multiple profit centers to a
file (TargetFile). This works great and it runs the query (Create Sheets)
for each profit center.

Do While Counter < LastRecord Or Counter = LastRecord

DoCmd.TransferSpreadsheet acExport, 8, "Create Sheets", TargetFile
DoCmd.GoToRecord acDataForm, "Group", acNext
vGroup = Forms![Group]![Group]
Counter = Counter + 1

Loop

The problem is it keeps exporting the data to the same same sheet in the
TargetFile, so I'm left with the data for the last profit center on a sheet
called "Create Sheets".

What I would like to do is add the data to a sheet in TargetFile and rename
it with the vGroup variable (below). I think then it will keep adding
separate sheets for each profit center.

Any suggestions or ideas on how to accomplish this would be appreciated.
Thanks for all of the help.
 
Although Access Help says you can't use a range name for an export, it does
work, at least in AC2003, I don't know about prior versions. The True means
the first row is field names, It that is not the case, change it to false.

Do While Counter <= LastRecord

DoCmd.TransferSpreadsheet acExport, 8, "Create Sheets", TargetFile, _
True, VGroup
DoCmd.GoToRecord acDataForm, "Group", acNext
vGroup = Forms![Group]![Group]
Counter = Counter + 1

Loop
 
Back
Top