Results 1 to 2 of 2
  1. #1
    cybercynic is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    2

    Dynamically Change Externally Linked File Folder

    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.

  2. #2
    cybercynic is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    2
    I now have my form set up with a drop down list.
    From my research each linked table has a TableDef and it appears I should be able to manipulate the TableDef for the files I want to change.
    Am I on the right track thinking I could set a variable based on a value from my form and use it (the variable) in the TableDef path?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA code to open a file with a dynamically changing filename
    By orangeman2003 in forum Programming
    Replies: 1
    Last Post: 09-09-2014, 06:17 PM
  2. Replies: 3
    Last Post: 01-24-2014, 03:33 PM
  3. Replies: 2
    Last Post: 12-27-2012, 09:37 AM
  4. Replies: 21
    Last Post: 08-20-2012, 11:59 PM
  5. Replies: 1
    Last Post: 12-13-2011, 08:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums