Transferring spreadsheets from Access to Excel

[EliseFreedman]

I have created a query in microsoft Access. I am then using the DoCmd.OutputTo command to transfer the query results into Excel ready for further manipulation. I basically have ended up with several different spreadsheet files(one of each salesman). Is there any MS Access code which would allow me to create up an excel workbook and then create each spreadsheet as a new worksheet in a single excel workbook so that ultimately I end up with 1 workbook containing 8 worksheets instead of 8 different spreadsheet files

 

[combo]

Something like:

[tt]xlStr = "C:\data_out.xls"
With DoCmd
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your_table1", xlStr, True
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your_query1", xlStr, True
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your_table2", xlStr, True
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your_query2, xlStr, True
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your_query3", xlStr, True
End With[/tt]

will create one workbook ("data_out.xls&quot with worksheets containing tables or query results (with named as source object), in excel 2000 format, i.e. with 64k rows in each page.

combo

 

[SkipVought]

Elsie,

You might make out alot better if you were to PULL the data from Access into Excel, rather than PUSH the data the other way.

I am a BIG proponent of...

1. NOT duplicating data and
2. NOT proliferating similar data into multiple sheets or workbooks

If you were to access Access using your query, you could, with a SINGLE SHEET, SINGLE QUERY, report any and all salesman on that sheet. Use the Data/External Data... menu item.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

I agree with Skip...

to make an ODBC connection to from excel to access...

in a blank excel worksheet, from the menu...

Data>get external data>new database query...follow the wizard, or select the use MS query option (I like the MsQuery, it's an interface similar to access),...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

when the data is returned, you can right click in the data area and set properties such as refresh on open, auto fill formulas, etc...

now when ever the database is updated the workbook will reflect the changes.(if you refresh the link...auto or manually)

Note: If you have prompt paramters in your access query,...you will need to move them to MsQuery.