Exporting Access data to different worksheets on the same workbook

  • Thread starter Thread starter chill
  • Start date Start date
C

chill

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
 
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",
Click to expand...
acFormatXLS, "C:\Data\Table1.xls",
True

DoCmd.OutputTo acQuery, "Table2",
Click to expand...
acFormatXLS, "C:\Data\Table2.xls",
True

DoCmd.OutputTo acQuery, "Table3",
Click to expand...
acFormatXLS, "C:\Data\Table3.xls",
True

I would very much appreciate all suggestions and help!!!

Thanks,
chill.


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
Click to expand...
step guide to creating financial statements
 
Back
Top