Open a Recordset from a form as an Excel spreadsheet

[tmort]

I have a query by form where the subform is a filtered recordset based on
query paramaters from the main form. I'd like to add a button to the form
that will re-run the query and open this recordset as an Excel spreadsheet
for further manipulation. The command button starts a macro to rerun the
query and then run the function below that I've tried to adapt from some code
found in another forum. It opens Excel and names the columns but it is not
populating the sheet.

The code is below. Any help will be appreciated. Thanks:

Option Compare Database



Public mobjXL As Excel.Application







Public Function ExportToExcel(strqrySQL As String)
Dim rst As ADODB.Recordset
Dim intCount As Integer

Dim mobjXL





' Create the Excel object

Set mobjXL = New Excel.Application
Set rst = New ADODB.Recordset




' Fetch the recordset
With rst
.ActiveConnection = CurrentProject.Connection.ConnectionString
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.MaxRecords = 65000 ' Approx max number of rows Excel can handle
.Open strqrySQL

End With

With mobjXL
' Add a workbook and turn off Excel updates
.ScreenUpdating = True
.Visible = False
.Workbooks.Add
.DisplayAlerts = False

' Add the column headers
For intCount = 0 To rst.Fields.Count - 1
.Cells(1, intCount + 1).Value = rst.Fields(intCount).name
Next intCount



' Dump the recordset to Excel

.Range("A2").CopyFromRecordset rst



.Visible = True

End With


' Add your error handler


End Function