export to Excel

  • Thread starter Thread starter Crystal
  • Start date Start date
C

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
 
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.
 
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
 
Back
Top