ACC2000: Using Automation to Transfer Data to Microsoft Excel

This article shows you how to create two user-defined functions for copying records from a Microsoft Access recordset to a Microsoft Excel spreadsheet and printing the updated spreadsheet. The functions use OLE Automation to transfer the data to Excel.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Copying One Field from One Record

To take data from a text box on a form and to place it in a cell in an existing Excel spreadsheet, formatting the text as bold, follow these steps:

1. Start Microsoft Excel and create a new spreadsheet. Save the spreadsheet on drive C as Ole_test.xls, and then quit Excel.
2. Start Microsoft Access and open any database. Create a new form not based on any table or query. Create a new text box on the form, and then set the Name property of the text box to ToExcel.
3. Add a command button to the form.
4. Type the following code for the OnClick event procedure of the command button:

   Dim mysheet As Object, myfield As Variant, xlApp As Object
   
   ' Set object variable equal to the OLE object.
   Set xlApp = CreateObject("Excel.Application")
   ' Set mysheet = GetObject("c:\ole_test.xls", "excel.sheet").
   Set mysheet = xlApp.workbooks.Open("c:\ole_test.xls").Sheets(1)
   
   ' Put the value of the ToExcel text box into the cell on the
   ' spreadsheet and make the cell bold.
   myfield = Me!ToExcel
   mysheet.cells(1, 1).Value = myfield
   mysheet.cells(1, 1).font.bold = True
   
   ' Set the Visible property of the sheet to True, save the
   ' sheet, and quit Microsoft Excel.
   mysheet.Application.windows("ole_test.xls").Visible = True
   mysheet.Application.activeworkbook.Save
   mysheet.Application.activeworkbook.Close
   xlApp.Quit
   
   ' Clear the object variable.
   Set mysheet = Nothing
   					

5. View the form in Form view. Type any text in the text box, and then click the command button.
6. Start Microsoft Excel and open the Ole_test.xls spreadsheet. Notice that the text that you typed in the text box is displayed in cell A1.

Copying an Entire Recordset

To create the function for transferring an entire recordset to Excel, follow these steps:

1. Open the sample database Northwind.mdb.
2. Create a new form not based on any table or query.
3. Add a command button to the form, and then type the following code for the OnClick event procedure of the command button:

   Dim DB As DAO.Database, Rs As DAO.Recordset
   Dim i As Integer, j As Integer
   Dim RsSql As String
   Dim CurrentValue As Variant
   Dim CurrentField As Variant
   Dim Workbook As Object
   Dim xlApp As Object
   Dim Sheet As Object
   
   Set DB = DBEngine.Workspaces(0).Databases(0)
   
   RsSql = "SELECT * FROM [Order Details] WHERE [OrderId]< 10249;"
   
   Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)
   Set xlApp = CreateObject("Excel.Application")
   xlApp.workbooks.Add
   Set Sheet = xlApp.activeworkbook.sheets(1)
   j = 1
   
   ' Loop through the Microsoft Access field names and create
   ' the Microsoft Excel labels.
   For i = 0 To Rs.Fields.Count - 1
       CurrentValue = Rs.Fields(i).Name
       Sheet.cells(j, i + 1).Value = CurrentValue
   Next i
   
   j = 2
   
   ' Loop through the Microsoft Access records and copy the records
   ' to the Microsoft Excel spreadsheet.
   Do Until Rs.EOF
       For i = 0 To Rs.Fields.Count - 1
           CurrentField = Rs(i)
           Sheet.cells(j, i + 1).Value = CurrentField
       Next i
       Rs.MoveNext
       j = j + 1
   Loop
   
   ' Print the Microsoft Excel spreadsheet.
   Sheet.PrintOut
   
   ' Close workbook without saving.
   xlApp.activeworkbook.saved = True
   Set Sheet = Nothing
   xlApp.Quit
   Set xlApp = Nothing
   					

4. View the form in Form view, and then click the command button.

NOTE: The above code causes the Excel spreadsheet to be printed. You do not see Excel unless you had Excel open before you clicked the command button.