Forum Discussion
Getting updated data from a Microsoft Form's SharePoint Excel Repository using VBA.
I want to grab data from an Excel file in SharePoint that is synced to a Microsoft Form and put it into a different file using a VBA macro, but I don't get the updated data. I have to manually access the Excel file that is a repository of the Microsoft Form Responses and let it sync to update. Then I can run the VBA. But I want to cut that manual step out. How can I do this?? I've tried many things (Power Automate, VBA updates, etc.), but no luck so far. I saw that I may have to use forms inserted through Excel online to get a file that refreshes immediately after each Microsoft Form submission, but that would mean I have to rewrite the Form and the VBA I currently have.
9 Replies
Thank you SergeiBaklan. I appreciate your response. You saved me a lot of time because my next step was to create a new form in Excel online and update my VBA.
I will read the blog you've provided to understand the other possible workarounds.
Use Power Automate to open the file (even a dummy step like reading the file) — this forces SharePoint to sync the latest Form responses before your VBA runs. If you’re sticking with VBA only, you’d have to manually open the file in Excel Online or on your PC before running the macro. There’s no purely VBA method to trigger the sync automatically.
piper977 , could you please share bit more details. Did you use desktop flow or cloud flow and which exactly steps. At least with cloud flow I was not able to push syncing the file.
I had to set up a cloud flow. The main aim is to use a dummy step that opens the SharePoint file which starts the sync between SharePoint and Excel Online.
Roughly, I arrange my text editor and command line how I describe here.
Any type of trigger, manual, scheduled or another type, can be set in Time Machine.
Step: Go to the Excel file in SharePoint, then use the “Get file content” or “Get file metadata” action.That step makes SharePoint automatically update the file with the newest Form information. Once you finish these steps, run your VBA on the desktop using the new file.
It does not solve everything, but it helps me a lot.
How about by VBA:
Workbooks.Open "https://your-sharepoint-url/FormsResponses.xlsx" ActiveWorkbook.RefreshAllI tested this and, unfortunately, the VBA just doesn't seem to be able to sync the Excel file with MS Forms. The Excel file in SharePoint where the MS Form is tied, does not refresh unless I manually open the file and it syncs upon opening.
Refresh and Sync are different mechanisms, we can't sync the file using refresh.
Previous year Microsoft changed the way how Forms is synced with Excel file How to update to the new solution for syncing forms responses to Excel | Microsoft Community Hub , now it syncs only if file is opened. Form created from Excel online doesn't sync automatically any more as well.
Workaround could be collect responses with Power Automate in any Excel file which is kept on SharePoint. Not ideal solution, but it could be suitable for the majority of cases.
Someone suggested to use OfficeScript run by Power Automate which writes any value at any cell in Excel file linked with Form, but I didn't test is it work or not.
More details in the blog above and related discussion.
