Link Excel Tables Programmatically

[Steve P]

Hi,
I have a project that performs simple comparative analysis on data contained
in two static, archieved excel workbooks.

I want to write some vba that will aid in the linking of the external
tables, so people less familiar with Access can perform the analysis.

I'd like for the clicking of a command button to prompt the user to select
which excel file they'd like to link (through the file select dialog box),
then link the table to the database....seems pretty simple...i think. lol.

Please help.

 

[Ken Snell]

See this article for a starting point. Instead of using acImport, use acLink
as the first argument for the TransferSpreadsheet step.

Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrowseFile