(Solved) – VBA code to refresh a specific external data connection

  • by

I have an Excel file (2007) that has 50 connections and takes about 5 minutes to refresh all. The workbook is designed to be refreshed on a daily basis by the users. The thing is that not all of these 50 connections are ready to be refreshed in the morning. Some are ready and some are not, depending on the server status. To help the users, I created a table that shows an estimated time to refresh (let’s call it ETR table), which takes a second to refresh. So I want the users to refresh only this ETR table while there are still pending works in the server. Once they have green lights on all the connections in ETR table, they can refresh all.

The problem is that the main sheet to which the users have access is protected and cannot have any external connections (otherwise, the table won’t refresh). Therefore, I placed the ETR table in another hidden but unprotected sheet and shows the values in the main sheet using formulas. Since they cannot access to ETR table directly, there’s no good way for the users to refresh only the ETR table.

Therefore, I’d like to make a command button (or anything that’d work) where the users can just click and it leads to refreshing just one specific table. Can anybody please help me here with the VBA code? I am very new to VBA, and have been researching it for days but I could only find codes to refresh Pivot tables, not external data connection.

I appreciate your time in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *