ACC: How to Use DAO to Link an Excel Spreadsheet

• Microsoft Access versions 7.0, 97

SUMMARY

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

You can use data access objects (DAO) to programmatically link a Microsoft Excel spreadsheet using Microsoft Visual Basic for Applications code.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Start Microsoft Excel.



2. On Sheet1 of a new workbook, type the following in cells A1:C3:




      A1: First   B1: Last    C1: Middle.
      A2: Adam    B2: Smith   C2: A.
      A3: Bob     B3: Jones   C3: B. 

3. Save the workbook as LinkTest.xls in the folder My Documents.



4. Close the workbook and quit Microsoft Excel.



5. Start Microsoft Access and create a new database.



6. Create a new module and type the following procedure in it:




      Sub XLLink(strNewAccTable as string, strXLFileName as String, _
           strImportSheet as String)

         ' Variables:
         '   strNewAccTable - the name of your new linked table.
         '   strXLFileName - the path and name of your Excel file. This
         '                   should be in the form "C:\MyDir\MyFile.xls."
         '   strImportSheet - the name of the sheet you want to link.
         ' All these variable are strings, and should be supplied to the
         ' subroutine enclosed in quotation marks.

         On Error GoTo XLError

         Dim db As DATABASE
         Dim td As TableDef

         Set db = CurrentDb

         ' Create a new TableDef using the passed name.
         Set td = db.CreateTableDef(strNewAccTable)

         ' Set the ConnectString property to the Excel file to link.
         ' In Microsoft Access 7.0, the ConnectString needs to reflect the
         ' version of Excel. Remove the apostrophe from the Excel 5.0
         ' line and comment out the Excel 8.0 line when working with
         ' Excel 5.0/95.
         ' td.Connect = "Excel 5.0;DATABASE=" & strXLFileName & ";"
         td.Connect = "Excel 8.0;DATABASE=" & strXLFileName & ";"
         td.SourceTableName = strImportSheet & "$"

         ' Append the new TableDef to the TableDefs collection.
         db.TableDefs.Append td

      Exit_XLLink:
         Exit Sub

      XLError:
         MsgBox Err.Number & " " & Err.Description
         Resume Exit_XLLink

      End Sub 

7. On the Debug menu, click Compile Loaded Modules.



8. On the Tools menu, click Debug Window.



9. In the Debug window, type:




XLLink "New Link", "C:\My Documents\LinkTest.xls", "Sheet1"

REFERENCES

For more information about connection strings, search the Help Index for "Connect Property," or ask the Microsoft Access 97 Office Assistant. For more information about TableDefs, search the Help Index for "DAO collections, TableDefs," or ask the Microsoft Access 97 Office Assistant.

For additional information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

Q163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: wordcon inf vba

Keywords          : 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999