I'm very new to PowerShell, my apologies in advance if I'm missing something obvious.
With my PowerShell script I want to do the following to a particular Excel file:
- Open that file.
- Refresh that file's data connection.
- Save that file.
- Then close that file.
An issue that I run into when I do this is that this file has some protections in place where I have to specifically select the option to open the file in edit mode, as seen below.
I've tried numerous suggestions, similar to what's been recommended in this link but the file always seems to remain in read-only mode, and I'm unable to save the file.
This is the code that I've created so far. If you have any suggestions on how I can open this file in read/write mode - your suggestions are welcomed.
$excel = New-Object -comobject Excel.Application
$excel.DisplayAlerts = $false
$FilePath = "C:\....\....\etc\chosen_file.xlsx"
$workbook = $excel.Workbooks.Open($FilePath, $null, $true)
$excel.Visible = $true
$connections = $workbook.Connections
$workbook.RefreshAll()
$excel.ActiveWorkbook.SaveAs($FilePath)
$workbook.Close()
$excel.quit()