This will paste in the single row, but it will also include the column headings. Set the
column widths as appropriate, and then delete the data row, leaving the headings only.
Save the spreadsheet asMyRecSet. This now provides a blank template to load your data
into. Make sure you close this file, otherwise it will have a lock on it.
Copy the following code into the module you created earlier in this chapter.
Sub CreateSpreadsheet 1 ()
Dim RecSet As Recordset
Dim MyExcel As Excel.Application
Dim MyBook As Workbook
Dim MySheet As Worksheet
Set MyExcel = CreateObject("Excel.Application")
Set MyBook = MyExcel.Workbooks.Open("MyRecSet", , True)
Set MySheet = MyBook.Worksheets( 1 )
Set RecSet = CurrentDb.OpenRecordset("customers")
MySheet.Range("a 2 ").CopyFromRecordset RecSet
On Error Resume Next
Kill "TestExcel.xlsx"
On Error GoTo 0
MyBook.SaveAs ("TestExcel")
MyBook.Close
MyExcel.Quit
Set RecSet = Nothing
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
End Sub
This code works similar to the earlier example in that it creates an Excel application, but
this time it loads in your Excel file called MyRecSet as a read-only file. The recordset is now
copied to cell A2 to allow for the column headings. A further enhancement is that the Kill
command is used to delete the previously saved file.
Run the code by clicking anywhere on it and pressingF5, or clicking the green triangle
(Run) icon on the toolbar. The code will take a few seconds to run since a fair amount of data
is being transferred. Do not forget to make sure you have added a reference to the Excel
Object Model, as shown on the first page of this chapter.
If you open TestExcel, you will now see that your data is far more user-friendly, with
column headings and column widths set correctly. This data could easily form the basis for
a pivot table, and all you would need to do is incorporate the pivot table into the Excel file
MyRecSet.
Chapter 35: Use Excel For Output 331