I have an Access database with externally linked Excel files.
I use multiple directories with the same Excel filenames like this:
C:\Data\Folder1\File A.xlxs
C:\Data\Folder1\File B.xlxs
C:\Data\Folder2\File A.xlxs
C:\Data\Folder2\File B.xlxs
I have set MS Access up with external links to:
- C:\Data\Folder_X\File A.xlxs and
- C:\Data\Folder_X\File B.xlxs
So to switch source files, before I start Access, I manually change the folder name to "Folder_X" of the files I want to use.
In Access I have a form button that I press to run a macro which imports the data.
This works, but I would like to improve on it.
Objective:
Generate a drop down list from a query that would allow me to select which directory to use.
To populate the drop down list, I will have another linked Excel file with all the directory names on it, and which will be in a fixed location.
So the idea is I will have a form with the drop down list of possible directories.
When I select the directory, I would like it to update the external file links - just as if I was doing it manually via the link manager.
Ideally I want to replace the last folder in the path shown in the Linked Table Manager with a variable, which is set by the drop down list form.
Once the variable is set, I want to use my form button to import the data.
Any help would be appreciated.