ACC: Programmatically Link or Embed an Object in a Form (95/97)

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to programmatically link or embed an object in an unbound object frame on a form using the object frame properties in Microsoft Access 7.0 and 97.

MORE INFORMATION

You can set the object frame Action property at run time to perform a number of operations on an object frame. These operations include the ability to link and embed objects in an object frame, as well as other operations for programmatic access to OLE functionality.

There are other object frame properties that you must set before you set the Action property. Those other properties depend on the type of OLE object you are working with, and the type of action you want to perform using the Action property.

Linking or Embedding an OLE Object

To link or embed an OLE object in an unbound object frame on a form, first set the following properties of the frame:

• OLETypeAllowed: Set to acOLELinked to indicate the OLE field will contain a linked object, acOLEEmbedded for an embedded object, or acOLEEither for either linked or embedded.
• SourceDoc: Set to the path and file name of the file to be linked or embedded. Do not set this property if you are creating an empty OLE object.
• Class: Set to the class name of the file extension. You can get the class from the Windows registry file in HKEY_CLASSES_ROOT listed under the file extension. You can also find the information in the documentation for the application supplying the object. This property may not be required, depending upon the OLE server and object being used.
• SourceItem: Indicates the portion of data in the source document to link to. For example, it can be a cell or cell range in a Microsoft Excel spreadsheet or a bookmark in a Microsoft Word for Windows document. This property setting is optional.

The following methods show examples of both linking and embedding an OLE object in an unbound object frame.

Method to Link an OLE Object

1. Start Microsoft Excel.

2. Create a new spreadsheet with the following entries:

          A1 : Name             B1 : Salary  C1 : Dept.   D1 :  Qty Sold
          A2 : Nancy Davolio    B2 : 16,000  C2 :     9   D2 :        19
          A3 : Andrew Fuller    B3 : 25,000  C3 :     3   D3 :       129
          A4 : Michael Suyama   B4 : 28,500  C4 :     3   D4 :       234
          A5 : Janice Leverling B5 : 30,000  C5 :    12   D5 :       199
          A6 : Linda Callahan   B6 : 50,000  C6 :     9   D6 :       126
          A7 : Johnathan King   B7 : 50,000  C7 :    12   D7 :        45
   
   

3. Save the spreadsheet as TestOLEAuto.xls, and then quit Microsoft Excel.

4. Start Microsoft Access.

5. Create a new form not based on any table or query in Design view.

6. Add an unbound object frame control to the detail section of the form.

7. In the Insert Object dialog box, click Create New, and then select Microsoft Excel Worksheet in the Object Type box. Click OK, and then quit Microsoft Excel when it starts.

8. Set the Name property of the unbound object frame to OLEExcelSheet.

9. Add a command button to the form, and set its Name property to cmdOLEAuto.

10. Set the OnClick property of the cmdOLEAuto command button to the

        following event procedure:
    

           Private Sub cmdOLEAuto_Click()
    

              On Error GoTo Error_cmdOleAuto_Click
              With Me![OLEExcelSheet]
                 .Enabled = True
                 .Locked = False
                 ' Specify what kind of object can appear in the field.
                 .OLETypeAllowed = acOLELinked
                 ' Class statement--optional for Excel worksheet.
                 .Class = "Excel.Sheet"
                 ' Specify the file to be linked.
                 ' Type the correct path name.
                 .SourceDoc = "c:\<pathname>\TestOLEAuto.xls"
                 ' Range statement--optional for Excel worksheet.
                 .SourceItem = "R1C1:R7C4"
                 ' Create the linked object.
                 .Action = acOLECreateLink
                 ' Optional size adjustment.
                 .SizeMode = acOLESizeZoom
              End With
           Exit_cmdOLEAuto_Click:
              Exit Sub
           Error_cmdOleAuto_Click:
              MsgBox CStr(Err) & " " & Err.Description
              Resume Exit_cmdOLEAuto_Click
           End Sub
    
    

11. Save the form as frmOLEAutoLink.

12. Open the form in Form view and click the cmdOLEAuto command button.

        Note that the TestOLEAuto.xls spreadsheet appears in the form.
    

Method to Embed an OLE Object

1. Start Microsoft Word.

2. Create a new document and type some text into it.

3. Save the document as TestOLEAuto.doc, and then quit Microsoft Word.

4. Start Microsoft Access.

5. Create a new form not based on any table or query in Design view.

6. Add an unbound object frame control to the detail section of the form.

7. In the Insert Object dialog box, click Create New, and then select Microsoft Word Document in the Object Type box. Click OK, and then quit Microsoft Word when it starts.

8. Set the following properties for the unbound object frame:

         Unbound Object Frame
         --------------------
         Name: OLEWordDoc
         Height: 11"
   

9. Add a command button to the form, and set its Name property to cmdOLEAuto.

10. Set the OnClick property of the cmdOLEAuto command button to the

        following event procedure:
    

           Private Sub cmdOLEAuto_Click()
              On Error GoTo Error_cmdOLEAuto_Click
              With Me![OLEWordDoc]
                 .Enabled = True
                 .Locked = False
                 ' Specify what kind of object can appear in the field.
                 .OLETypeAllowed = acOLEEmbedded
                 ' Class statement for Word document.
                 .Class = "Word.Document"
                 ' Specify the file to be embedded.
                 ' Type the correct path name.
                 .SourceDoc = "c:\<pathname>\TestOLEAuto.doc"
                 ' Create the embedded object.
                 .Action = acOLECreateEmbed
                 ' Optional size adjustment.
                 .SizeMode = acOLESizeZoom
              End With
           Exit_cmdOLEAuto_Click:
              Exit Sub
           Error_cmdOLEAuto_Click:
              MsgBox CStr(Err) & " " & Err.Description
              Resume Exit_cmdOLEAuto_Click
           End Sub
    
    

11. Save the form as frmOLEAutoEmbed.

12. Open the form in Form view and click the cmdOLEAuto command button.

        Note that the TestOLEAuto.doc document appears in the form.
    

REFERENCES

For more information about the Action property, search the Help Index for "Action property," or ask the Microsoft Access 97 Office Assistant.

For more information about OLE linking and embedding, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q123859
   TITLE     : ACC: Sample OLE Automation for MS Word and MS Excel

   ARTICLE-ID: Q132003
   TITLE     : ACC: How to Save a Copy of an Embedded MS Word Document

   ARTICLE-ID: Q114214
   TITLE     : ACC2: How to Programmatically Embed or Link an Object in a
               Form

Keywords          : kbole kbprg PgmHowTo FmsHowTo IntpOle
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto