Powershell - Suppress Excel Dialog Box - Merge Changes with Server

Question

I have Powershell script that calls an Excel VBA script and saves the file. I'm running into an issue while closing the workbook as I get a dialog box "Would you like to merge your changes with the latest updates on the Server?" and PowerShell waits for user confirmation. How do I suppress this notification? I have enabled displayAlerts = $false but that doesn't seems to be working in this case

The file sits on a shared onedrive folder location which is prompting Excel to ask this question

 $file="test - Copy v4.xlsm"
 $x1 = New-Object -ComObject "Excel.Application"
 $x1.displayAlerts = $false # don't prompt the user
 $x1.Visible = $false
 $wb = $x1.workbooks.Open($file)
 $x1.Run('MACRO_NAME')
 $wb.Save()
 $wb.Close()
 $x1.Quit()
 Remove-Variable wb,x1

Does it work if you use SaveAs() to the same path? As an aside, I prefer using this module to the ComObject - ImportExcel — Ash
It errors out at Cannot access 'vb test - Copy v5.xlsm'. At line:26 char:5 + $wb.SaveAs("C:\vb t ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException — shreyasfifa
Try this - SaveAs($filename, 52, [Type]::Missing, [Type]::Missing, $false, $false, 1, 2) - Workbook.SaveAs. Parameters explained named in order FileName, FileFormat (52 for xlsm), Password (Can't be null, so use Type.Missing), WriteResPassword (Type missing again), ReadOnlyRecommended, CreateBackup, Access Mode (1 - do not change access mode), ConflictResolution (2 - User changes always accepted). You can see links to some of the values on the linked page. — Ash
If I use SaveAs($filename) the document gets saved to the My Documents Folder instead of the folder path that it is originally in. I tried passing the full path of the file and I get this error Unable to get the SaveAs property of the Workbook class At line:1 char:1 + $wb.SaveAs("C:\Users\*\OneDrive \*\vb test ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException — shreyasfifa

shreyasfifa shreyasfifa · Accepted Answer · 2021-05-21T18:25:22

I might have stumbled upon a solution. Instead of using Save or SaveAS and trying to pass parameters, I used the Workbook.Close Method and passed True as a parameter which solved my problem

Please note that if others are making changes on Excel online, this might result in merge issues. There's a checkin() and checkout() method that I'm trying to implement which should take care of this problem as well: Opening an Excel document from SharePoint using PowerShell

 $file="test - Copy v4.xlsm"
 $x1 = New-Object -ComObject "Excel.Application"
 $x1.displayAlerts = $false # don't prompt the user
 $x1.Visible = $false
 $wb = $x1.workbooks.Open($file)
 $x1.Run('MACRO_NAME')
 $wb.Save()
 $wb.Close($true)
 $x1.Quit()
 Remove-Variable wb,x1

Powershell - Suppress Excel Dialog Box - Merge Changes with Server

1 Answers