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


---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access version 2.0
---------------------------------------------------------------------

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:

1. Start Microsoft Excel and create a new spreadsheet. Save the
   spreadsheet as C:\ACCESS2\OLE_TEST.XLS.

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

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

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

5. Insert the following code in the procedure:

      Dim mysheet As Object

      'Set object variable equal to the OLE object.
      Set mysheet = GetObject("c:\access2\ole_test.xls", "excel.sheet")

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

6. Close the form module.

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