The option for "Refresh this connection on Refresh All' keeps getting unchecked, how do I find the cause?

Question

I am having this problem with Excel. I posted before but never saw any answers.

We have about 15 spreadsheets we use that are all connected to an SQL database. Each spreadsheet has 8 connections that pull data from the database. We have a VB program that runs every hour to update the data. The program opens each spreadsheet and calls Refresh all. Been working fine for years.

We also have a manual program that tells the VB program to refresh all. We use that because the users of the spreadsheet can't use refresh all.

About a year ago now, the refresh all setting started getting unchecked. When it does, it is on all the connections except 1. That one connection doesn't pull any new or current data.

It's usually most of the worksheets that the flag gets unchecked on, not always all of them.

So I've been leaning towards something with bad data could be the cause. It doesn't do it when you manually select refresh all so must be something when the VB program runs is causing it. Where would I look to find the cause?
I have a Macro that I run that sets the flag back on but it continues to break.

Previous post:
https://learn.microsoft.com/en-us/answers/questions/5431124/the-option-for-refresh-this-connection-on-refresh

Answer 1

Hi @Matt From Boston,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your sharing, the behavior is that it works when manually clicking Refresh All but it fails when your VB automation runs. I suspect the issue is not due to "bad data", but it can relate to timing/background refresh behavior.

When Excel runs Refresh All, some connection types can refresh in the background. Objects with the BackgroundQuery setting enabled can refresh “in the background,” meaning the call to RefreshAll can return before all connections actually finish updating.

Therefore, I suggest you can first try checking with one affected workbook:

• Run your VB automation but temporarily remove/disable Save + Close after RefreshAll and let Excel finish refreshing completely. After that, save the workbook manually to see if the issue persists.
• In the meantime, also check each connection properties via Data > Queries & Connections > right-click connection > Properties > Uncheck "Enable background refresh" and keep "Refresh this connection on Refresh All" checked.

Please understand that the initial response does not always resolve the issue immediately. However, you can try these steps and let me know if it works for you. If not, we can work together to investigate further.

If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

The issue you are experiencing with the "Refresh this connection on Refresh All" option getting unchecked may be related to how the VB program interacts with the Excel files. Since the problem does not occur when you manually select refresh all, it suggests that the automated process might be causing the connections to lose their settings.

Here are a few areas you might want to investigate:

1. Connection Settings: Ensure that the connection settings in your VB program are correctly configured. If the program is modifying the connections in a way that causes them to lose their refresh settings, that could be the root cause.
2. Error Handling: Check if there are any errors occurring during the refresh process in the VB program. If an error occurs, it might be affecting the state of the connections.
3. Data Quality: As you mentioned, bad data could potentially be causing issues. Review the data being pulled from the SQL database to ensure it is valid and does not contain any anomalies that could disrupt the refresh process.
4. Macro Interference: Since you have a macro that sets the flag back on, ensure that it is not conflicting with the VB program. It may be worth reviewing the macro's logic to ensure it is not inadvertently causing the refresh settings to be unchecked.
5. Excel Version and Updates: Ensure that you are using a supported version of Excel and that it is fully updated. Sometimes, bugs in older versions can lead to unexpected behavior.
6. Testing in Isolation: Try running the VB program on a single spreadsheet to see if the issue persists. This can help isolate whether the problem is with a specific spreadsheet or the overall process.

By investigating these areas, you may be able to identify the cause of the issue and implement a solution to prevent the refresh settings from being unchecked in the future.