Exporting to Seperate Excel Worksheets within the same workbook

[Dimonet]

I am trying to script a macro that will export several tables into seperate worksheets within the same workbook. Is there are way to perform this command?

 

[lespaul]

I'm not sure you can do that in a macro, you may have to do it in VBA. there's a command that will take an existing macro and convert it to VBA and then you would just need to modify the code to the specific sheet.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lespaul]

I think I was mistaken, in the Access VBA Forum (Forum705) I found:

DoCmd.TransferSpreadsheet acImport, 8, "lb", "c:\temp\td v sd.xls", False, "lb$"
DoCmd.TransferSpreadsheet acImport, 8, "sheet2", "c:\temp\td v sd.xls", False, "sheet2$"

where "lb$" and "sheet2$" are the sheet names, apparently you will need the $ at the end of the sheet name.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[JerryKlmns]

When ever you export a table or a query to an existing excel workbook, if the name of the table or query doesnt exist as a worksheet name then you get a new worksheet with that name.
If the name does already exist then data is overwritten.

 

[Dimonet]

My code isn't erroring out but my spreadsheet isn't populating with anything. Any idea? Which value is the table name I am exporting from? I tried changing the true / false but that didn't help. Am I doing something wrong? I copied and pasted what I am working with in the module I am calling.

Sub Export_Excel()
DoCmd.TransferSpreadsheet CONFT_Audit_Rule_Configuration, 8, "CONFT_Audit_Rule_Configuration", "M:\Concur_Upgrade\Configuration_Report\master_report.xls", True, "CONFT_Audit_Rule_Configuration$

 

[PHV]

DoCmd.TransferSpreadsheet [!]acExport[/!], 8, "CONFT_Audit_Rule_Configuration", "M:\Concur_Upgrade\Configuration_Report\master_report.xls", True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886