open access recordset as an excel spreadsheet

[tmort]

I have a query by form where I would like to open the recordset that
comprises the subform in Excel if the user wishes to further analyze the data.

I have some code from elsewhere that will open Excel and label the columns
but I am not getting the recordset into the spreadsheet.

The code follows, 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



' 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

 

[Norman Goetz]

On Fri, 18 Mar 2005 18:03:02 -0800, tmort wrote:
Option Compare Database

Public mobjXl As Excel.Application

Public Function ExportToExcel(strQrySQL As String)
Dim rst As ADODB.Recordset
Dim intCount As Integer
Set mobjXl = New Excel.Application
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
'.ConnectionString
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open strQrySQL
End With
With mobjXl
.ScreenUpdating = True
.Visible = False
.Workbooks.Add
.DisplayAlerts = False
For intCount = 0 To rst.Fields.Count - 1
.Cells(1, intCount + 1).Value = rst.Fields(intCount).Name
Next intCount
.Range("A2").CopyFromRecordset rst, 65000, 255
.Visible = True
End With
End Function

Norman Götz