Forum Discussion
Need unattended "Workbook Links" refresh all
I am League Secretary for 110 teams spread across 24 communities nationwide, and have a master spreadsheet with external links to 50 distributed spreadsheets, with many non-technical users of the distributed files also being able to view the merged results at the following link.
https://1drv.ms/x/c/ae1a2ff31aaaff91/ER65sA9AWB1JrjXrKTs_NG8BuHA5HwggNPGyPwOdzKhaAA
Those users barely know how to do simple data entry of bowling scores into their distributed file, and otherwsie see how well they are doing in competition with other teams by clicking on the shared link for the master spreadsheet to choose their community (c00 through c49).
When changes are made to any distributed file I need the master file updated. It works when I open the master spreadsheet, and works when I manually select "Data" / "Workbook Links" and select "Refresh All" in the pop up "Workbook Links" window.
However, I cannot sit in front of my computer all day manually refreshing. I have a life and am doing other activities while the master spreadsheet is open in the background, and I need Ecxel to automagically do a "Workbook Links" "Refresh All" at some unattended intervals.
I keep seeing answers like, "you can schedule automatic data refreshes by setting up a refresh interval in the "Connections" section under the Data tab." Clicking on "Queries and Connections" above "Workbook Links" opens a window offering two tabs, but the Queries" tab shows 0 Queriees, and the "Connections" tab shows 0 Connections. Perhaps the answer is for someone to teach me how to add something under either tab.
Otherwise, clicking on "Workbok Links" opens the "Workbook Links" window for the manual Refresh All, but I am stuck finding anything to chose to set up some kind of automatic Refresh All.
I see possibilities using VBA, (e.g., https://www.exceldemy.com/auto-refresh-excel-file-without-opening/)
But I am afraid that requires activating Macros for non-technical users with the shared link to the master spreadsheet., so am ignoring any VBA suggestions.
Does anybody have a non-VBA solution?
With apologies to everyone, I thought one of a dozen different VBA versions would continue working afer I announced I had it working. Nope! Please ignore any of my VBA / Macro replies.
I finally asked copilot for a different solution because everything previously given to me by copilot (and all other web research) always eventully failed.
So copilot taught me about power queries, which other responses and research gave as solutions, but at the time I was not ready to go off into new territory. This time I tackled the query solution and it has been woking flawlessly all day. I even installed it without users noticing the difference except that all the data is now being automatically updated instead of waiting for my next Date / Workbook Links / Refresh All. That old way was a pain and is now obsolete.
Hooray!🤩
3 Replies
click on the workbook settings always trust the workbook link that will always refresh you workbook links
If this resolves your query don't forget Mark as Solution.
After more days of trying I could not find a non-VBA solution, so I researched what using macros would do to not-tech remote users of the shared links and learned some reassuring and interesting information worth sharing.
First, my concern macros would break my system for remote users doing "anyone can edit" changes was unfounded. Users never see that my spreadsheet has macros. Instead, they simply view the synchronized results from my end that are macro-independent. Macros at my end never get activated by any remote shared access edits. So, I proceeded. Now instead of using any suggested code, I asked Copilot to generate the VBA code for me.
Since, my macros are only activated when I make edits on my own PC, that turned into additional research on how to add VBA code to test every 15 seconds on my end whether some user had edited a relevant cell on their end (since their edit would not trigger the macro). Using a timed trigger on my end solved the problem of showing remote user the correctly synchronizedd macro results.
With apologies to anyone that thinks this is a sales pitch, I found Copilot in Excel is AMAZING, and over the last 12 hours I have spent hours in detailed conversations with it tweaking what my code needed to do to get the exact results I wanted. Each test led to more questions and proposed alternatives, each time copying the generated code into various modules (with Copilot telling exactly where and how to place the code into modules). Copilot turned out to be a patient and knowledgeable tutor on topics thay included how to get the code working with my spreadsheet where sheets are hidden and/or protected with passwords on both the sheets and the workbook.
Bottom line, with zero prior VBA experience, I am still playing with it. I expect the final code will be as good as anything found on the web to automatically refresh workbook links since it will be tested and working for conditions most users won't need to worry about. I will post what I decided to use in my next reply.
With apologies to everyone, I thought one of a dozen different VBA versions would continue working afer I announced I had it working. Nope! Please ignore any of my VBA / Macro replies.
I finally asked copilot for a different solution because everything previously given to me by copilot (and all other web research) always eventully failed.
So copilot taught me about power queries, which other responses and research gave as solutions, but at the time I was not ready to go off into new territory. This time I tackled the query solution and it has been woking flawlessly all day. I even installed it without users noticing the difference except that all the data is now being automatically updated instead of waiting for my next Date / Workbook Links / Refresh All. That old way was a pain and is now obsolete.
Hooray!🤩
