Forum Discussion
Need unattended "Workbook Links" refresh all
- Mar 19, 2025
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!🤩
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!🤩