Hello,
I manage over 400+ Microsoft Forms, and stopped synching after Jan 13, 2025. The associated Power Automate flows, Power BI queries, and dashboards data is not updating unless the linked Excel files are manually opened. Manually opening 400 files just to trigger the sync is not practical.
To address this in my environment, I created a PowerShell script that loops through a specified directory containing the Form-linked Excel files. The script programmatically opens each file to force the data sync, then closes the file. This process is automated via Task Scheduler and runs once daily.
This is a temporary workaround until Microsoft resolves the New Sync bugs.
Below is the script — you can modify the folder path to match your setup.
In my case, the process took about 55 minutes to open and close all files. Your runtime may vary depending on the number of form response files you have.
Hope this helps, and feel free to reach out if you have any questions.
Thanks,
Rodrigo Pareno - Systems Analyst
# MS Form and Excel Data Refresh 02/27/2025
# Prerequisites - Install OneDrive on the server or computer and ensure the SharePoint folder is showing. Create a task in Task Scheduler to run the PowerShell script daily or every 3 hours.
# Start OneDrive and ensure it is running
$OneDriveProcess = Get-Process -Name "OneDrive" -ErrorAction SilentlyContinue
if (-not $OneDriveProcess) {
***Start-Process "C:\Program Files\Microsoft OneDrive\OneDrive.exe"***
***Start-Sleep -Seconds 10 # Allow time for OneDrive to start***
***Write-Host "OneDrive launched."***
} else {
***Write-Host "OneDrive is already running."***
}
# Set path to the folder containing the Excel files
$folderPath = "C:\Users\JohnWick\OneDrive - Data\Documents\FormResponses"
# Get all Excel files from the folder and subfolders, excluding specific folders
$excelFiles = Get-ChildItem -Path $folderPath -Recurse -Filter *.xlsx | Where-Object {
***$filePath = $\_.DirectoryName***
***$filePath -notlike "$folderPath\Folder1\*" -and***
***$filePath -notlike "$folderPath\Folder2\*" -and***
***$filePath -notlike "$folderPath\Folder3\*" -and***
***$filePath -notlike "$folderPath\Folder4\*"***
}
# Function to open and close Excel files
function Open-And-CloseExcelFiles {
***param(***
***[array]$files***
***)***
***# Start Excel application***
***$excel = New-Object -ComObject Excel.Application***
***$excel.Visible = $true***
***foreach ($file in $files) {***
***try {***
***# Open Excel file***
***$workbook = $excel.Workbooks.Open($file.FullName)***
***Write-Host "Opened file: $($file.FullName)"***
***} catch {***
***Write-Host "Error opening file: $($file.FullName). Skipping this file."***
***continue # Skip this file and move to the next***
***}***
***# Wait for 10 seconds (optional, depending on sync needs)***
***Start-Sleep -Seconds 10***
***try {***
***# Close the Excel file without saving changes***
***$workbook.Close($false) # False means don't save changes***
***Write-Host "Closed file: $($file.FullName)"***
***} catch {***
***Write-Host "Error closing file: $($file.FullName)"***
***}***
***}***
***# Quit Excel application***
***$excel.Quit()***
}
# Process all files and stop after the last file
Open-And-CloseExcelFiles -files $excelFiles
Write-Host "All files processed. Exiting the script."
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
