How do I Create a Link to an External Spreadsheet Via VBA & Access?

The VBA method "TransferSpreadsheet" can link Excel data to an Access table.

When Excel users need Access's more extensive set of database tools to manipulate their spreadsheet data, they can click Access' "Excel" icon in the "Import" panel to duplicate the spreadsheet data as a new Access table. But if users instead want to maintain a single copy of the data, they can do so by creating a link rather than a separate table. Using VBA to create this link relieves Access users of having to create it manually, a process requiring the selection of options. VBA code will choose the correct options, lessening the chance of possible user error.

Instructions

1. Open Access, then enter the Visual Basic integrated development environment (IDE) by pressing "Alt-F11." Insert a new code module by selecting "Module" from the IDE's "Insert" menu.

2. Paste the following subroutine into any portion of the new code module:

   Public Sub xstest()

   DoCmd.TransferSpreadsheet acLink, 5, _

   "newtable", "<INSERT_COMPLETE_PATH_NAME_TO_EXCEL_SPREADSHEET_FILE>", True, "A1:C4"

   End Sub

3. Type over the text "<INSERT_COMPLETE_PATH_NAME_TO_EXCEL_SPREADSHEET_FILE>" (without the quotation marks) the full path name of the Excel spreadsheet you want Access to link to. Also, type over the text "A1:C4" the full range specification, including any field headings, of the table in the spreadsheet that you want the link to refer to.

   This subroutine creates a link from the Excel spreadsheet you specify to a table called "newtable." Access VBA will automatically create this table to display the link's data. The function argument "acLink" tells VBA to create a dynamic link, rather than import and make a copy of the data.

4. Click on any of the subroutine's statements, then press "F5" to run the subroutine. Press "Alt-F11" to re-enter the standard Access environment, then double-click the "newtable" icon in the "All Tables" pane at the left of the screen. Access will display the table's data, which will be the same as that of the spreadsheet when viewed in Excel. (Note that Microsoft has disabled the feature allowing Access to edit the spreadsheet data.)

5. Press "Alt-F11" to re-enter the Visual Basic IDE, then make the following changes to the "xstest" subroutine: Change the text "acLink" to read "acImport," and change "newtable" to read "newtable2." These changes will instruct VBA to create a new Access table that imports, rather than links to, the spreadsheet data. This action will allow Access users to modify the new table's data (though these modifications will not be reflected in the spreadsheet).

6. Run the subroutine as you did in step 4, then press "Alt-F8" to return to Access. Double-click the "newtable2" icon in the screen's left pane, then edit the new table as needed.

References

• Microsoft: The Transfer Spreadsheet Method
• Microsoft: Changing Access Tables That Are Linked to an Excel Workbook