Public Sub CreateExcelInfo()
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Const sFileNameTemplate As String = "PathAndNameXLS file"
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from qTheQuery" 'This has to be the name of the query your report is using to display data
With oExcel
.Visible = True
'Create new workbook from the template file
Set WB = .Workbooks.Add(sFileNameTemplate)
With WB
Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet
With WS
objRs.Open sSQL, objConn, adOpenStatic, adLockReadOnly
Set rng = .Range("A1") 'Starting point of the data range
rng.CopyFromRecordset objRs
objRs.Close
End With
End With
.Quit
End With
Set objConn = Nothing
Set objRs = Nothing
End Sub