Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting to Seperate Excel Worksheets within the same workbook

Status
Not open for further replies.

Dimonet

IS-IT--Management
Oct 17, 2001
65
US
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?
 
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
 
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
 

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.
 
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$
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top