Microsoft Access VBA Macro Programming

Enter the following code into your module:

Sub CreateSpreadsheet()

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.Add

Set MySheet = MyBook.Worksheets( 1 )

Set RecSet = CurrentDb.OpenRecordset("customers")

MySheet.Range("a 1 ").CopyFromRecordset RecSet

MyBook.SaveAs ("TestExcel")

MyBook.Close

MyExcel.Quit

Set RecSet = Nothing

Set MyExcel = Nothing

Set MyBook = Nothing

Set MySheet = Nothing

End Sub

This code creates an Excel object and opens a new blank workbook. It then creates a

Recordsetobject based on the Customers table in the Northwind database and copies this

to cell A1 on the first worksheet in the workbook. The new workbook is then saved to the

default folder and the Excel application is closed.

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.

If you open the new workbook in Excel, you will find that the orders table has been copied

into the first sheet of the workbook, as if you had done a copy and paste from Access.

However, you may notice that no column headings have been provided and column widths do

not always allow the data to be seen.

Using an Existing Spreadsheet as a Template

A way to make the transfer tidier is to load in an existing spreadsheet that already has the

headings and column widths set, but no data in it.

To do this, create a blank spreadsheet in Excel. Open the Customers table in Access in View

mode by clicking the View icon in the Views group of the ribbon and selecting the first row.

Make a copy of the row and then go to your Excel spreadsheet. Paste the row into Excel

as text.`

330 Microsoft Access 2010 VBA Macro Programming