+ Reply to Thread
Results 1 to 2 of 2

VBA Macro to refresh all external data sources in a different workbook

  1. #1
    Registered User
    Join Date
    12-20-2019
    Location
    London, England
    MS-Off Ver
    MS O365
    Posts
    4

    VBA Macro to refresh all external data sources in a different workbook

    Dear all,

    I am currently working with very heavy files which are all connected to Access databases which require daily updates and I would like to remotely do that on a different PC.

    What I would like to do is have one Excel Workbook in which I can define the folder path in which the workbooks are supposed to get refreshed (Data-> refresh all), saved and closed.

    I would like to define the folder path in a specific cell within the excel instead of having to go to the vba code every time.

    Could anybody assist me with this?

    Much appreciating your support!

    Thanks,

    BorniBorn
    Attached Files Attached Files
    Last edited by borniborn; 12-20-2019 at 09:18 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: VBA Macro to refresh all external data sources in a different workbook

    The following is based on altering the connection string to point to the folder that contains the database. Go to Data->Queries and Connections to see your connections. I have one there called qry_names. If you right-click on the connection and select properties you will see a tab that says Definition. Mine looks like this:

    DSN=MS Access Database;DBQ=C:\Users\Dan\Documents\Home\Database\Contacts List.accdb;Default=C:\Users\Dan\Documents\Home\Database;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

    There is actually an ODBC; in front of all this.

    The name of the DB is Contacts List.accdb. The trick is to substitute the rest of the path to it and the default to the current path.

    As a matter of convenience, I gave a name to cell F2 on Sheet 1. I called it Path_Name. Now that it has a name, you can change the sheet name or move the cells around. The code will know where to find the range called Path_Name.

    The way the code works is that I split the connection strings into an array using ";" as the delimiter. I also use the split command to get the database name. Then I assign the path and database names to the two pieces of interest in the connection string: elements 2 and 3 of the array. Then I glue it all back together using JOIN.

    Your will have to change the first line to set the connection name to the name of the query you want to refresh.

    Please Login or Register  to view this content.
    If your connection string does not look like mine, I will give you a short piece of code and some instructions on how to get it. The principle will be the same, we just might be working with different pieces.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Workbook Update on external sources
    By zplugger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-25-2019, 09:51 AM
  2. Replies: 2
    Last Post: 08-31-2019, 12:26 AM
  3. Replies: 1
    Last Post: 03-15-2019, 09:26 AM
  4. Pause macro while data refresh from external source completes
    By Kestis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2016, 04:42 AM
  5. Macro to refresh all (external links & pivot tables) in a workbook every 5 mins.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2014, 01:17 AM
  6. Replies: 0
    Last Post: 05-27-2013, 05:28 AM
  7. Automatically refresh data from external sources
    By janvanman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2012, 07:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1