Here is a fairly complex piece of code I use to create an Excel spread sheet
using ADO. There are extra comments throughout to help you.
HTH,
Bob
Public Function CreateExcelSS()
'-------------------------------------------------------------
' Purpose : Uses ADO to export two record sets to Excel.
' Sheet1 (renamed 'Results') receives cross tab query
' recordset. Sheet2 is created, renamed 'Gene_Ref', &
' receives Gene & Ref recordset values for cross tab output.
' Both sheets columns and headings are formatted:
' Headings are bolded and AutoFit executed on columns.
' Author : Bob Orta, Tuesday, July 08, 2003
'-------------------------------------------------------------
' Revision History
'-------------------------------------------------------------
' Tuesday, July 08, 2003 RO:
' 10/21/03 - Added LocusID to second sheet of worksheet.
'=============================================================
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xl2Sheet As Excel.Worksheet
Dim i As Integer
Dim FC As Byte ' # fields from crosstab query.
On Error GoTo HandleErr
Set xlApp = New Excel.Application ' Create Excel Application object
Set xlBook = xlApp.Workbooks.Add ' Create a new workbook
xlApp.DisplayAlerts = False
xlApp.DisplayAlerts = True
xlApp.Worksheets.Add ' Adds a worksheet.
' Capture reference to first worksheet
Set xlSheet = xlBook.ActiveSheet
xlSheet.Name = conSheetName ' Change the worksheet name
' Create recordset
Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst.Open _
Source:=conQuery, _
ActiveConnection:=CurrentProject.Connection
FC = rst.Fields.Count
With xlSheet
For i = 1 To FC
' Copy field names to Excel using count of fields, which is
necessary because
' the number of output fields in a crosstab query is not fixed.
' Bold the column headings and insert field names. Starting
position A1. The variable 'i'
' advances the cursor one cell to the right for each additional
field.
With .Cells(1, i)
.Value = rst.Fields(i - 1).Name
.Font.Bold = True
End With
Next
' Copy all the data from the recordset into the spreadsheet.
.Range("A2").CopyFromRecordset rst
' Format the data.
' Causes all columns to autofit cycling throug accordint to field
count (FC).
For i = 1 To FC
.Columns(i).AutoFit
Next
.Columns(2).Delete ' The second column of the crosstab data is not
desired and deleted.
End With
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
' Create 2nd recordset
rst2.Open _
Source:="qryGeneRef", _
ActiveConnection:=CurrentProject.Connection
xlBook.Worksheets("Sheet1").Activate
' Capture reference to second worksheet.
Set xl2Sheet = xlBook.ActiveSheet
xl2Sheet.Name = "Gene_Ref" ' Renames worksheet.
With xl2Sheet
With .Cells(1, 1) ' First cell values
.Value = rst2.Fields(0).Name
.Font.Bold = True
End With
With .Cells(1, 2) ' Second cell values.
.Value = rst2.Fields(1).Name
.Font.Bold = True
End With
With .Cells(1, 3) ' First cell values
.Value = rst2.Fields(2).Name
.Font.Bold = True
End With
.Range("A2").CopyFromRecordset rst2
.Columns("A:B").AutoFit ' Auto fit for two columns.
End With
rst2.Close
'Display the Excel chart
xlApp.Visible = True
Set rst2 = Nothing
Set xl2Sheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
ExitHere:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Form_frmAgExportTestEXCELOutput.CreateExcelSS"
Set rst = Nothing
Set rst2 = Nothing
Set xlSheet = Nothing
Set xl2Sheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Select
End Function
