Forum Discussion
Getting updated data from a Microsoft Form's SharePoint Excel Repository using VBA.
How about by VBA:
Workbooks.Open "https://your-sharepoint-url/FormsResponses.xlsx"
ActiveWorkbook.RefreshAll
I 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.
