Here is code you can use to create a spredsheet from a recordset


Dim rs_Data As ADODB.Recordset
Dim str_Select As String

Dim rs_Cols As ADODB.Recordset
Dim str_Cols As String

Dim objExcel As Excel.Application

Dim numRows As Integer
Dim numCols As Integer

Dim row As Integer
Dim col As Integer

On Error GoTo Err_SS_Recordset

   Screen.MousePointer = vbHourglass

'
'   Create recordset
'
'    str_Select = "Put your select string here"
   str_Select = "SELECT * FROM myTable"
   Set rs_Data = New ADODB.Recordset
   rs_Data.CursorLocation = adUseClient
   rs_Data.Open str_Select, cn, adOpenStatic, adLockReadOnly
   If (rs_Data.RecordCount <= 0) Then
       MsgBox "No data to export to Excel for your filter.", vbOKOnly, "No Data"
       rs_Data.Close
       Set rs_Data = Nothing
       Exit Sub
   End If
   
On Error GoTo Err_Create_Rpt_Spreadsheet

'
'   Initialize number of rows and columns
'
   numCols = rs_Data.Fields.count
   numRows = rs_Data.RecordCount
   
'
'   Create Excel Spreadsheet
'
   Set objExcel = New Excel.Application
   objExcel.Workbooks.Add

'
'   Set defaults for the spreadsheet
'
   objExcel.Cells.Select
   With objExcel.Selection.Font
       .Name = "Arial"
       .FontStyle = "Regular"
       .Size = 9
       .Strikethrough = False
       .Superscript = False
       .Subscript = False
       .OutlineFont = False
       .Shadow = False
       .Underline = xlUnderlineStyleNone
       .ColorIndex = xlAutomatic
   End With

'
'   Open form showing export progress
'    FOrm has a progress bar on it.  Code can be deleted
   'frm_Export2Excel.Show
   'frm_Export2Excel.lbl_Exp2Excel.Caption = "Header Row  "
   
'
'   Load column headers
'    Special code to add column headers from a table I have
'    Delete but note how the headers are added
'
   'str_Cols = "SELECT  *  FROM  view_Excel_Col_Info where txt_ViewName = '" & VIEW_NAME & "'"
   'Set rs_Cols = New ADODB.Recordset
   'rs_Cols.Open str_Cols, cn, adOpenStatic, adLockReadOnly
   'row = 1

'
'   Use the information from the Excel Column Information table if it exists.
'   Use the view field name otherwise
'
   'If (rs_Cols.RecordCount > 0) Then
   '    For col = 1 To numCols
   '        rs_Cols.MoveFirst
   '        rs_Cols.Find "txt_FieldName = '" & rs_Data.Fields(col - 1).Name & "'"
   '        objExcel.Cells(row, col) = Trim(rs_Cols!txt_ColName)
   '        objExcel.Range(objExcel.Cells(row, col), objExcel.Cells(row, col)).Select
   '        objExcel.Selection.ColumnWidth = rs_Cols!int_ColWidth
   '        If (InStr(rs_Cols!txt_ColName, "Date")) Then
   '           objExcel.Range(objExcel.Cells(row + 1, col), objExcel.Cells(row + rs_Data.RecordCount, col)).Select
   '           objExcel.Selection.NumberFormat = "dd-mmm-yyyy"
   '        End If
   '    Next col
   'Else
'
' Column Headers using the table column names
'
       For col = 1 To numCols
           objExcel.Cells(row, col) = Trim(rs_Data.Fields(col - 1).Name)
           objExcel.Range(objExcel.Cells(row, col), objExcel.Cells(row, col)).Select
           objExcel.Selection.ColumnWidth = 8
       Next col
   End If
   frm_Export2Excel.pb_Exp2Excel.Value = 100# * row / (numRows + 1)
'
'   Close the column information recordset
'
   'rs_Cols.Close
   'Set rs_Cols = Nothing

'
'   Load selected records into rows
'

   rs_Data.MoveFirst
   For row = 2 To numRows + 1
       frm_Export2Excel.lbl_Exp2Excel.Caption = "Row " & str(row - 1) & " of  " & str(numRows)
       frm_Export2Excel.pb_Exp2Excel.Value = 100# * row / (numRows + 1)
       For col = 1 To numCols
           objExcel.Cells(row, col) = rs_Data.Fields(col - 1).Value
       Next col
       objExcel.Rows(row).Select
       objExcel.Selection.RowHeight = 15
       rs_Data.MoveNext
   Next row

'
'   Activate the Excel spreadsheet
'
   Unload frm_Export2Excel
   
   objExcel.Range("A1").Select
   objExcel.Visible = True
   Screen.MousePointer = vbDefault

Exit_btnCancel_Click:
   rs_Data.Close
   Set rs_Data = Nothing
   Set objExcel = Nothing
   Exit Sub
   
Err_SS_Recordset:
   MsgBox Err.Description
   Set rs_Data = Nothing
   Screen.MousePointer = vbDefault
   Unload frm_Export2Excel
   Exit Sub

Err_Create_Rpt_Spreadsheet:
   MsgBox Err.Description
   objExcel.Visible = True
   Unload frm_Export2Excel
   Resume Exit_btnCancel_Click
I'll add code to build a word table in the next comment

Go with above, or even give a try to Virtual Print Engine, Crystal Report and Excell object will load very slow due to its big foot print.

Moderator, my recommended disposition is:

    Accept Dhaest's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer