cybercynic
New member
- Local time
- Yesterday, 23:23
- Joined
- Jun 19, 2016
- Messages
- 1
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
FWIW I posted on MS ACCESS forums, but no replies - hoping I have better luck here.
TIA for any help.
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
Code:
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
TIA for any help.