Change Path to External File via a Form

[cybercynic]

I have an Access 2010 database with externally linked Excel files.
I use multiple directories with the same Excel filenames like this:
C:\Data\Folder1\File A.xlsx
C:\Data\Folder1\File B.xlsx
C:\Data\Folder2\File A.xlsx
C:\Data\Folder2\File B.xlsx
C:\Data\Folder3\File A.xlsx
C:\Data\Folder3\File B.xlsx
etc.

I have set MS Access up with external links to:
- C:\Data\Folder_X\File A.xlsx and
- C:\Data\Folder_X\File B.xlsx
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.
Essentially I want to replace the last folder in the path shown in the Linked Table Manager with a variable (or replace the entire path), which is set by the drop down list form.
Once the path variable is set, I want to use my form button to import the data.

Progress:
So far I have set up the "static" Excel file with the directories i.e. Folder1, Folder2, Folder3 etc.
Set up a form with a drop down list to pull in the directory values.

Next Steps:
- Set path variable
- apply path variable to TableDefs for files File A.xlsx and File B.xlsx i.e. connect to database
- refresh links

From my research, in theory I know what needs doing, but since this is my first attempt at VBA scripting, I am in need of some help.
The below code (copied from the internet) is pretty close to what I need, except...
- I only want to relink specific named files
- I need to create a path variable from my drop list

 Sub RelinkTables(path,db)
    Dim dbs As Database
    Dim tdf As TableDef
    ' Loop through all tables in the database.
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        ' If the table has a connect string, it's a linked table.
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & Path & "\" & db
            Err = 0
            On Error Resume Next
            tdf.RefreshLink         ' Relink the table.
            If Err <> 0 Then
            End If
       End If
    Next tdf
  End Sub

FWIW I posted on MS ACCESS forums, but no replies - hoping I have better luck here.
TIA for any help.

 

[moke123]

these links may be helpful..

http://access.mvps.org/access/api/api0002.htm

http://allenbrowne.com/ser-59.html

 

[sneuberg]

Maybe you can use something from the code at this link

https://github.com/digitalspenz/rcmodule/blob/master/basJStreetAccessRelinker.bas