Exporting data from Access to individual worksheets in Excel

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

Guest

I have a query within Access which extracts information on territories. These
territories are listed from 1 through to 10. What I need is for the raw
information from the access query to be then exported into Excel. But with
all the information regaridng territory 1 to appear within worksheet 1,
territory 2 information to appear within worksheet 2 and so on...... Is it
possible someone could give me the code, a pointer or the terminology on what
I am trying to achieve. Many thanks
 
haven't tried this myself BUT...
according to the access help, "Microsoft Access copies the exported data to
the next available new worksheet in the workbook."

therefore I'd create the workbook with ten blank worksheets in, each named
appropriately.

then run the transferspreadsheet ten times, using different query criteria
for each one. One simple way to do this is to have a territory field on your
form, which your query uses as a selection criterion...
Set this field to territory one.
Then run the first transfer spreadsheet
Set the field to territory two.
Then run the second.. and so on.

Personally I would probably would just have done it with ten separate
workbooks..!
 
JackP said:
haven't tried this myself BUT...
according to the access help, "Microsoft Access copies the exported data to
the next available new worksheet in the workbook."

therefore I'd create the workbook with ten blank worksheets in, each named
appropriately.
Click to expand...
TransferSpreadsheet is indeed the way to go, but naming the sheets in
the workbook in advance will not do the job! "Next available" is
probably misleading; any existing sheet, whether having any data in it
or completely blank, is not considered available as per the quote from
help above. At the first export Access will create a new sheet and name
it after the exported table or query, then from the second export on it
will overwrite that same sheet.

The trick here is to use the "Range" argument, in spite of help saying
it is not used in export; the argument passed will be the name of hte
sheet created (if none exists by that name) or overwritten (if the name
already exists).

Also note that there is no need to create the workbook manually up
front, if it does not exist Access VBA will create it for you.

By the way, if territory is always going to be 1 trough 10, there is no
point in changing the territory parameter and exporting manually. Use
some simple VBA code to do it all for you! Sample:

Function export_territory_data()
Dim sQuery As String, sFile As String
sQuery = "MyQuery" 'Name Of Query To Export
sFile = "C:\SomeFolder\SomeFile.xls" 'Target path and file name
For i = 1 To 10
Forms![FormName]![ControlName] = i 'put actual names here
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
sQuery, sFile, True, "Territory " & i
Next
End Function

As long as you have the form open when you execute the code, it will do
the rest. You could add a command button on the form to trigger the
code; in that case, paste the code directly in the button's Click event
(skipping the first and last line).

HTH,
Nikos
 
Has anyone had a problem or know a fix on exporting Access to Excel using
'checkbox fields'? When I did this export, the fields were left out.

Thanks
Jimmy

Nikos Yannacopoulos said:
haven't tried this myself BUT...
according to the access help, "Microsoft Access copies the exported data to
the next available new worksheet in the workbook."

therefore I'd create the workbook with ten blank worksheets in, each named
appropriately.
Click to expand...
TransferSpreadsheet is indeed the way to go, but naming the sheets in
the workbook in advance will not do the job! "Next available" is
probably misleading; any existing sheet, whether having any data in it
or completely blank, is not considered available as per the quote from
help above. At the first export Access will create a new sheet and name
it after the exported table or query, then from the second export on it
will overwrite that same sheet.

The trick here is to use the "Range" argument, in spite of help saying
it is not used in export; the argument passed will be the name of hte
sheet created (if none exists by that name) or overwritten (if the name
already exists).

Also note that there is no need to create the workbook manually up
front, if it does not exist Access VBA will create it for you.

By the way, if territory is always going to be 1 trough 10, there is no
point in changing the territory parameter and exporting manually. Use
some simple VBA code to do it all for you! Sample:

Function export_territory_data()
Dim sQuery As String, sFile As String
sQuery = "MyQuery" 'Name Of Query To Export
sFile = "C:\SomeFolder\SomeFile.xls" 'Target path and file name
For i = 1 To 10
Forms![FormName]![ControlName] = i 'put actual names here
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
sQuery, sFile, True, "Territory " & i
Next
End Function

As long as you have the form open when you execute the code, it will do
the rest. You could add a command button on the form to trigger the
code; in that case, paste the code directly in the button's Click event
(skipping the first and last line).

HTH,
Nikos
Click to expand...
 
Back
Top