Exporting to Excel Files

neoartz237

Tensai
Local time
Today, 04:58
Joined
Feb 12, 2007
Messages
65
Ok, I know its easy to export tables, queries and reports in excels files, but I cant export an excel file for all the tables in just one xls files with multiple sheets.

How can I do this? Please please help
 
You can do it using VBA.

Psuedo code would be:

1. Gather list of tables you want to export (if not all).
2. Create an Excel Workbook object.
-----
3. Get the name of the first/next table you want to export.
4. Create a recordset based on that table.
5. Create an Excel Worksheet object from the Workbook in Step 2.
6. Rename the Worksheet to the name of the table.
7. Use the .Range("A1").CopyFromRecordset rst to paste the table into that worksheet
8. Close the Worksheet
9. Move to the next table in the list.
------
10. Save the worksheet.

Done.

Post back if you need a hand but there's plenty of examples about to help you.

Pete.
 
Ummm I tried this "TransferSpreadsheet" method, the code went:

Dim tdf as TableDef
For Each tdf In CurrentDb
DoCmd.TransferSpreadsheet acExport, 8, tdf.Name, strSaveFileName
Next

All is good except the headers on the excel file (signifying the fieldnames), are all in original fieldnames, where in if I use this:

DoCmd.OutputTo acOutputQuery, "qryTKYOKUMD01_Err", acFormatXLS, , False

it automatically makes the headers the tagname of the field.

How can I change the headers from original names to tagnames? Do I really have to manually edit each cells?

Thanks
 

Users who are viewing this thread

Back
Top Bottom