Excel automation

[SF]

Hi,

I want to exprt my rst to an excel sheet, formatting the header but I am
stuck. The code is attached below and hope someone would help on this issue.

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

'Check if PNGO is not selected
If IsNull(Me.PNGO) Then
MsgBox "You must select a Partner before you can proceed..", vbOKOnly +
vbInformation, "PNGO not selected"
Me.PNGO.SetFocus
Exit Sub
End If
Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("MOFMissingReport")
qdf.PARAMETERS("ID") = ID
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
xlApp.Worksheets(1).Cells("A3").CopyFromRecordset rst

SF

 

[Tom van Stiphout]

You were very close. This worked for me:
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("qryTest1")
qdf.Parameters("parMaxID") = 5
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Cells(1, 1).value = "test"
xlSheet.Cells(2, 2).CopyFromRecordset rst

-Tom.
Microsoft Access MVP

 

[SF]

Hi Tom,

Thank you for your advide. Is there any book that is goog for handle Excel
programing (like formatting, merge cell, handle record set...) from Access
that you recommend? I will anticipate a lot of data exprot to excel in the
future

SF