Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting Access data to different worksheets on the same workbook
Hi there, I have three Access 97 tables that I am trying to export Excel fro VBA. The problem is I would like all three to be on the same workboo rather than on different workbooks (i.e., having one workboo containing three tabs each for a differnt table data) The code I am using right now which creates three different workbook are as below: DoCmd.OutputTo acQuery, "Table1", acFormatXLS, "C:\Data\Table1.xls" True DoCmd.OutputTo acQuery, "Table2", acFormatXLS, "C:\Data\Table2.xls" True DoCmd.OutputTo acQuery, "Table3", acFormatXLS, "C:\Data\Table3.xls" True I would very much appreciate all suggestions and help!!! Thanks, chill ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting Access data to different worksheets on the same workbook
Access 2002
Add a new module. Under Tools/references in the IDE that should open automatically, set a reference to the Microsoft Excel 10 Object Library then add this code Function Save_to_Excel() Dim XLapp As Excel.Application Dim xlWB As Excel.Workbook Dim xlWS As Excel.Worksheet Set XLapp = New Excel.Application Set xlWB = XLapp.Workbooks.Open("C:\Data\Temp.xls") Set xlWS = xlWB.Worksheets("Sheet1") Dim rst As Recordset Set XLapp = New Excel.Application Set rst = CurrentDb.OpenRecordset("table1") xlWS.Range("A6500").End(xlUp).Offset(1, 0).CopyFromRecordset rst rst.Close Set rst = CurrentDb.OpenRecordset("table2") xlWS.Range("A6500").End(xlUp).Offset(1, 0).CopyFromRecordset rst xlWB.Close True Set xlWS = Nothing Set xlWB = Nothing XLapp.Quit Set XLapp = Nothing End Function code tested ok This will get you started Patrick Molloy Microsoft Excel MVP -----Original Message----- Hi there, I have three Access 97 tables that I am trying to export Excel from VBA. The problem is I would like all three to be on the same workbook rather than on different workbooks (i.e., having one workbook containing three tabs each for a differnt table data) The code I am using right now which creates three different workbooks are as below: DoCmd.OutputTo acQuery, "Table1", acFormatXLS, "C:\Data\Table1.xls", True DoCmd.OutputTo acQuery, "Table2", acFormatXLS, "C:\Data\Table2.xls", True DoCmd.OutputTo acQuery, "Table3", acFormatXLS, "C:\Data\Table3.xls", True I would very much appreciate all suggestions and help!!! Thanks, chill. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting Current Access data into Template? | Excel Worksheet Functions | |||
exporting data from access to excel | Excel Discussion (Misc queries) | |||
Exporting data from access to excel | Excel Worksheet Functions | |||
Exporting data to access | Excel Programming | |||
Help exporting worksheets/data to a workbook. | Excel Programming |