Export to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have three separate queries in Access (97) that I need to export into an
Excel (2000) spreadsheet on three separate worksheets. I can get this to
work, but the trick is that I need the data to start at B2 (for example) on
each of the worksheets so that it lines up with other data already on the
worksheet.

Can anyone please advise on how to get this to work? I tried using
WorksheetC$B2 in the Range parameter of the TransferSpreadsheet function, but
to no avail.

Thanks
Dave
 
You can create an excel object, open your spreadsheet in there and use
Range.CopyFromRecordset method - I used it and it seems to be working just
fine...

HTH
 
Sergey Poberezovskiy said:
You can create an excel object, open your spreadsheet in there and use
Range.CopyFromRecordset method - I used it and it seems to be working just
fine...

HTH
Click to expand...

Thanks Sergey,
Have you got a sample of the code you used to open the xls within the excel
object?

Regards
Dave
 
Establishing the Excel object references:

'Set up the necessary objcts
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo Build_XL_Report_ERR
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Add

Copying the data to a specific location

Set qdf = CurrentDb.QueryDefs("qselSCCBrpt")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstSCCB = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
xlSheet.Cells(intX + 3, 1).CopyFromRecordset rstSCCB
lngDetailCount = rstSCCB.RecordCount
rstSCCB.Close
Set rstSCCB = Nothing
Set qdf = Nothing
 
Back
Top