Transfering table to same workbook, different worksheet

[Guest]

I need to be able to select data from an Access Table by week of entry and
export it to a different worksheet within the same workbook for example the
workbook would be named 2005Workbook and the Sheets would be named Jan, 2005,
Feb 2005, Mar, 2005 etc.
The docmd.transferspreadsheet doesn't seem to have a way to specify a
different worksheet within the same workbook. Have I missed something or is
there another way to do what I need to do?

 

[John Nurick]

Hi Harry,

Use TransferSpreadsheet's Range argument (even though Help says it
doesn't work). You'll need to append a ! or $ to the sheet name (I can
never remember which without trying it).

 

[Guest]

If you are proficient in VBA and understand the Excel Object Model, you could
do it by writing a procedure that would:
Start an instance of Excel
Open the Workbook
Select the Worksheet you want or create it if it does not exist
copy the data into the worksheet in the location you want it to be
save the workbook
close the workbook
close excel

 

[Guest]

Hi John,
Sorry it has taken so long. I tried your recommendation and the only thing I
get is the equivalent of a Not Found message for the sheet that I want to
transfer to. I made the range both ways !sheet1 and .sheet1 for the xls file
I want to transfer to. Do I need to put the full path and workbook name into
the string such as "c:\spreadsheets\spreadsheet.xsl!(.)sheet1????

 

[John Nurick]

Hi Harry,

As I said, I can never remember myself exactly what's needed. It seems
that in these particular circumstances you don't need either a $ or a !
(if you did it would be a matter of appending, not prepending). The
following works correctly in the Northwind sample database (at least in
Access 2003):

Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Customers", "C:\temp\nathan\multi.xls", True, "CustomersTable"
Docmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Orders",
"C:\temp\nathan\multi.xls", True, "OrdersTable"