Exporting to multiple sheets in Excel from Access

[David]

Is there a way to export an Access table such that the
value in a selected column can be used to identify
specific sheets in a workbook for the data? For example,
if the first field contains the values A, B and C. Is
there a way I can set up the export so all the A records
are exported to sheet A, the B records to sheet B and the
C records to sheet C in a workbook?

 

[John Nurick]

Hi David,

Set up three queries - let's call them qryA, qryB, qryC, one returning
each set of records. Then either:

-create a macro that calls the TransferSpreadsheet action three times,
once to export qryA to your desired workbook with a Range argument of A,
the second time to export qryB to the same workbook with a Range
argument of B, and so on; or

-use VBA code to call TransferSpreadsheet the three times, passing the
sheet name in the Range argument.