You can use this as a start for your needs. We use this to drop the results of a query onto an excel spreadsheet.
Dim Xfile As Excel.Application
Dim strQuery As String
Dim qdf As QueryDef
Dim PRM As Parameter
Dim Message
Dim qryRST As Recordset
Dim DB As Database
Dim ExcelWasRunning As Boolean
Dim bRunning As Boolean
Dim i As Integer
Dim j As Integer
Dim CurrentField As Variant
Dim CurrentValue As Variant
Dim ReportDate As Variant
Dim Sheet As Object
Dim CurrentDate As String
CurrentDate = Now()
Set DB = CurrentDb
ExcelWasRunning = False
strQuery = "qryCountDoltotals"
Set qdf = DB.QueryDefs(strQuery)
For Each PRM In qdf.Parameters
PRM.Value = Eval(PRM.Name)
Next PRM
Set qryRST = qdf.OpenRecordset(dbOpenSnapshot)
Message = MsgBox("Please Wait While The Excel Report Is Created", vbInformation)
FileCopy "C:\OOSIFTAaudits_DB\JurisdictionalAuditSummaries.xls", _
"C:\My Documents\JurisAuditSummary.xls"
On Error Resume Next
Set Xfile = GetObject(, "Excel.Application")
If Err.Number = 0 Then
ExcelWasRunning = True
Xfile.ActiveWorkbook.Save
Xfile.ActiveWorkbook.Close
Message = MsgBox("You Have An Excel File Open, Click OK, The File Will Be Saved And Closed.", vbOKOnly)
End If
Err.Clear
If Xfile Is Nothing Then
Set Xfile = CreateObject("Excel.Application")
bRunning = False
End If
Xfile.Workbooks.Open ("C:\My Documents\JurisAuditSummary.xls")
Xfile.Visible = True
Set Sheet = Xfile.ActiveWorkbook.Sheets(1)
j = 100
Do Until qryRST.EOF
For i = 0 To qryRST.Fields.Count - 1
CurrentField = qryRST(i)
Sheet.Cells(j, i + 1).Value = CurrentField
Next i
qryRST.MoveNext
j = j + 1
Loop
j = 2
i = 4
Sheet.Cells(j, i).Value = [Forms]![frmReports]![txtBgnDate] & " - " & [Forms]![frmReports]![txtEndDate]
Sheet.Cells(7, 9).select
qryRST.Close
Set qryRST = Nothing
Set DB = Nothing
Set qdf = Nothing
End If
