export to Excel

[Crystal]

I have two tables that I need to export to Excel
automatically. I have code that exports the table to a
spreadsheet.

Problem:

I need to export both tables into the same spreadsheet, so
they need to be separate worksheets. Is this possible?

Crystal

 

[Ken Snell]

You can't use TransferSpreadsheet to write to the same spreadsheet for
separate exports.

Can you use a union query that combines two queries (one for each table), or
use a query that combines records from both tables? Then just export that
new query.

 

[Guest]

Sure thing- the following is hard-coded for one pass thru,
but you could put the workbook name, sheet name and range
into a table to read into the procedure....

Set db = CurrentDb
Set objXL = New Excel.Application
Set rst = db.OpenRecordset("temptable")
conWKB_NAME = "G:\Directory1\Workbook.xls"
ConSHT_NAME = "Sheet1"
conRANGE = "DataInputRange"

With objXL
Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets(ConSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
objSht.Range(conRANGE).ClearContents
objSht.Range(conRANGE).CopyFromRecordset rst
End With