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
.