Ben Rapier
asked on
I am trying to create an Access query that will reference a cell in an Excel worksheet where the worksheet's file name is "Like" the table's primary key.
I am trying to create an Access query that will reference a cell in an Excel worksheet. There are hundreds of Excel files located in a single folder but I want to reference the one that has a file name matching the table's primary key. Something similar to: =(C:\folder\filename\sheet 1!A1) where filename is Like *SITE_ID*.xls. SITE_ID is a column in Table1. Any suggestions?
Is SITE_ID the complete filename except for the extension or are you really trying to do a fuzzy search?
You'll have to write a user-defined function in VBA to check all the files in folder that are based on the name and return the first one found.
ASKER
The SITE_ID column has hundreds of unique values but each one is in the format of '1234'. There is a corresponding Excel file for each site. The files are stored in the C:\design folder. An example file name is '1234.xls' and I am looking for the value of cell 'A1' on the 'Work' sheet (tab).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the prompt help! I guess I should have mentioned that I was working on an append table query.