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

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.