Link to home
Start Free TrialLog in
Avatar of Ben Rapier
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\sheet1!A1) where filename is Like *SITE_ID*.xls. SITE_ID is a column in Table1. Any suggestions?
Avatar of PatHartman
PatHartman
Flag of United States of America image

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.
Avatar of Ben Rapier
Ben Rapier

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
Avatar of ReneD100
ReneD100
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the prompt help! I guess I should have mentioned that I was working on an append table query.