I would recommend using VBA to build your Excel workbook.
Here is some code I wrote for that. It is configured for late binding, but
you can reference Excel and do early binding, which is much more convenient
for developing.
You should be able to modify this to accomodate your needs.
Code Start
==================
Public Sub ExportObjectToExcel(ObjectName As String, ExportFilename As
String, OpenAfterExport As Boolean)
Const PROC_NAME As String = "ExportObjectToExcel"
Dim oDatabase As DAO.Database
Dim oRecordSet As DAO.Recordset
Dim sSQL As String
Dim lRow As Long
Dim lCol As Long
Dim oWorkbook As Object
Dim oXLApp As Object
Dim oWorksheet As Object
Dim oRange As Object
' Early binding versions...
' Dim oWorkbook As Excel.Workbook
' Dim oXLApp As Excel.application
' Dim oWorksheet As Excel.Worksheet
' Dim oRange As Excel.Range
On Error GoTo ErrorHandler
If Len(Nz(ExportFilename)) = 0 Then
MsgBox "Please select an ExportTo filename first."
Exit Sub
End If
On Error Resume Next
Set oXLApp = CreateObject("Excel.application")
' Set oXLApp = New Excel.application
On Error GoTo ErrorHandler
If oXLApp Is Nothing Then
MsgBox "Excel is required for this functionality."
Exit Sub
End If
Set oDatabase = CurrentDb
sSQL = "SELECT * FROM " & ObjectName
Set oRecordSet = oDatabase.OpenRecordset(sSQL, dbOpenDynaset,
dbSeeChanges)
oXLApp.Workbooks.Add
oXLApp.ActiveWindow.DisplayGridlines = False
Set oWorksheet = oXLApp.ActiveWorkbook.Sheets(1)
With oWorksheet
For lCol = 0 To oRecordSet.Fields.Count - 1
.Cells(1, lCol + 1) = oRecordSet.Fields(lCol).NAME
Next
lRow = 2
' Loop through the Microsoft Access records and copy the records to
the Microsoft Excel spreadsheet.
Do Until oRecordSet.EOF
For lCol = 0 To oRecordSet.Fields.Count - 1
.Cells(lRow, lCol + 1) = oRecordSet.Fields(lCol).Value
' Set the number format
Select Case oRecordSet.Fields(lCol).Type
Case dbNumeric, dbSingle, dbBigInt, dbByte, dbInteger,
dbLong
.Cells(lRow, lCol + 1).NumberFormat =
"#,##0_);[Red](#,##0)"
Case dbBoolean
Case dbCurrency
.Cells(lRow, lCol + 1).NumberFormat =
"$#,##0.00_);[Red]($#,##0.00)"
Case dbDouble, dbFloat, dbDecimal
.Cells(lRow, lCol + 1).NumberFormat =
"#,##0.00_);[Red](#,##0.00)"
End Select
Next
oRecordSet.MoveNext
lRow = lRow + 1
Loop
' Get the headers range
Set oRange = .Range(.Cells(1, 1), .Cells(1, .UsedRange.Columns.Count))
' oRange.HorizontalAlignment = XlHAlign.xlHAlignCenter
oRange.HorizontalAlignment = -4108
oRange.Interior.Color = RGB(225, 225, 225)
oRange.Borders.LineStyle = 1
' oRange.Borders.LineStyle = XlLineStyle.xlContinuous
' Get the used range
Set oRange = .Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count,
..UsedRange.Columns.Count))
oRange.Columns.AutoFit
.Cells(2, 2).Select
oXLApp.ActiveWindow.FreezePanes = True
.PageSetup.Orientation = 2
' .PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = False
.PageSetup.FitToPagesWide = 1
.PageSetup.TopMargin = oXLApp.InchesToPoints(0.5)
.PageSetup.LeftMargin = oXLApp.InchesToPoints(0.5)
.PageSetup.RightMargin = oXLApp.InchesToPoints(0.5)
.PageSetup.BottomMargin = oXLApp.InchesToPoints(0.5)
End With
Cleanup:
On Error Resume Next
Set oWorksheet = Nothing
' Save workbook.
oXLApp.ActiveWorkbook.SaveAs ExportFilename
If Not oXLApp Is Nothing Then
If OpenAfterExport Then
oXLApp.Visible = True
Else
oXLApp.Quit
End If
End If
Set oXLApp = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , MOD_NAME & "."
& PROC_NAME
'Resume Next
GoTo Cleanup
End Sub
==================
Code End
