Export to a specific worksheet in workbook

[Jerid B]

Is it possible to export a query or report to a particular sheet within a
workbook?
I tried using a macro (OutputTo) and choose the output file to:
C:\Temp\Quote.xls"Data!"
Workbook name is Quote.xls and the sheet is data.
We have so many users that need this particular report, but only so many
copies of Access here.
I get an error saying Access can not save the outfut file and lists reasons.
All reasons listed I checked are legit except possibly the location because
of how I addressed the actual sheet in the output file.
Access 2003 - Excel 2003

 

[AFSSkier]

Do a Link Table to the Spreadsheet Tab. (make sure you have the field headers
in the spreadsheet. Then do an Update Query joining the [SourceTable] to the
[SpreadsheetTable]. You can then do an Append Query to add new records to
the spreadsheet with an Is Null expression.

If I don't have the field headers already in the spreadsheet, I do a Query
to create the 1st set of records (or 1 record to get only the fields), run,
copy & paste the results into the Spreadsheet.

 

[SusanV]

Hi Jerid,

Not sure if this will help you in your case, but if you use
TransferSpreadsheet rather than OutputTo, it will create a new spreadsheet
in an existing workbook. For example, I use the below code to first create a
new Excel file using OutputTo, then append another table to the Excel file
as a new sheet in the same book. (Note the file name is the same in both
actions, but data is from 2 separate tables.) The new worksheet will be
named the same as the source table or query.

DoCmd.OutputTo acOutputTable, "Legacy_Pump", acFormatXLS, "c:\temp\" &
VesselName & "_Pumps.xls", 0
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"HasPlan_Pump", "c:\temp\" & VesselName & "_Pumps.xls"

 

[Douglas J Steele]

Unfortunately, Access no longer has the ability to update linked Excel
spreadsheets (at least, Access 2002 and Access 2003 don't). This is due to a
lawsuit Microsoft recently lost.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Do a Link Table to the Spreadsheet Tab. (make sure you have the field headers
in the spreadsheet. Then do an Update Query joining the [SourceTable] to the
[SpreadsheetTable]. You can then do an Append Query to add new records to
the spreadsheet with an Is Null expression.

If I don't have the field headers already in the spreadsheet, I do a Query
to create the 1st set of records (or 1 record to get only the fields), run,
copy & paste the results into the Spreadsheet.
--
Thanks, Kevin

Is it possible to export a query or report to a particular sheet within a
workbook?
I tried using a macro (OutputTo) and choose the output file to:
C:\Temp\Quote.xls"Data!"
Workbook name is Quote.xls and the sheet is data.
We have so many users that need this particular report, but only so many
copies of Access here.
I get an error saying Access can not save the outfut file and lists reasons.
All reasons listed I checked are legit except possibly the location because
of how I addressed the actual sheet in the output file.
Access 2003 - Excel 2003

 

[Ron2006]

The advise from Douglas is relative to AFSSkier's suggestion.

In the transferspreadsheet acExport you can specify the "Tab" name
that you want the exported data to go to. It is called the "rangeName",
I believe. You can export/create as many tabs as Excell can handle all
in the same XLS file.

Ron

 

[Ron2006]

The advise from Douglas is relative to AFSSkier's suggestion.

In the transferspreadsheet acExport you can specify the "Tab" name
that you want the exported data to go to. It is called the "rangeName",
I believe. You can export/create as many tabs as Excell can handle all
in the same XLS file.

Ron

 

[Ron2006]

If you are going to be using the same workbook over and over you may
want to read the following. It describes some naming problems that you
may encounter with the tab name.

http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

Ron

 

[Jerid B]

Thanks for the info...
I decided to export to a workbook and overwrite each time it's updated.
Then I have another workbook with the fromat and am using a macro to update
from the spreadsheet that Access is exporting too. It works out rather
smoothly.