Microsoft KB Archive/114836

INF: How to Use OLE Automation to Transfer Data to MS Excel PSS ID Number: Q114836 Article last modified on 07-30-1994

2.00

WINDOWS

= SUMMARY =

In Microsoft Access version 2.0, you can use code to manipulate data in applications that support OLE Automation, such as Microsoft Word version 6.0 for Windows or Microsoft Excel version 5.0.

= MORE INFORMATION =

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the “Building Applications” manual.

The following example demonstrates how to take data from a text box on a form and place it in a cell on an existing Microsoft Excel spreadsheet, and then format the text as bold:

  Start Microsoft Excel and create a new spreadsheet. Save the spreadsheet as C:_TEST.XLS.   Start Microsoft Access and open any database. Create a new, unbound form. Create a new text box on the form. Set the text box’s Name property to ToExcel.   Create a command button on the form, without using the Command Button Wizard. If the property sheet is not open, open it by choosing the Properties button on the toolbar.   Create an OnClick event procedure for the command button by clicking in the button’s OnClick property field, choosing the Build button to the right of the field, selecting Code Builder, and then choosing OK.   Insert the following code in the procedure: Dim mysheet As Object ’Set object variable equal to the OLE object. Set mysheet = GetObject(&quot;c:_test.xls“,”excel.sheet&quot;) ’Put the value of the ToExcel text box into the cell on the ’spreadsheet and make the cell bold. mysheet.cells(1, 1).value = Me!ToExcel 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.[quit] ’Clear the object variable. Set mysheet = Nothing NOTE: The brackets around the word “quit” are necessary to specify that this is a Microsoft Excel method, rather than a Microsoft Access method.   Close the form module.   View the form in Form view. Type any text in the text box, and then click the command button. 

= REFERENCES =

Microsoft Access “Building Applications,” version 2.0, Chapter 13, “Communicating with Other Applications”

For more information about OLE Automation, search for “OLE Automation” then “Interoperability with Microsoft Word and Microsoft Excel” using the Microsoft Access Help menu.

For more information about the syntax used in communicating with an application via OLE Automation, see the documentation for that application.

Additional reference words: 2.00 KBCategory: KBSubcategory: IntpOthr

=
================================================================

Copyright Microsoft Corporation 1994.