Microsoft KB Archive/210288

= ACC2000: Using Automation to Transfer Data to Microsoft Excel =

Article ID: 210288

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210288



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



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



MORE INFORMATION
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:  Start Microsoft Excel and create a new spreadsheet. Save the spreadsheet on drive C as Ole_test.xls, and then quit Excel. 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. Add a command button to the form.  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  View the form in Form view. Type any text in the text box, and then click the command button. 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.</li></ol>

Copying an Entire Recordset
To create the function for transferring an entire recordset to Excel, follow these steps: <ol> Open the sample database Northwind.mdb.</li> Create a new form not based on any table or query.</li>  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 </li> View the form in Form view, and then click the command button.</li></ol>

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.

Keywords: kbhowto kbinfo kbfaq KB210288

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.