4

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.

enter image description here

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()
CrudeOperator
  • 91
  • 10
  • `$workbook = $excel.Workbooks.Open($FilePath, $null, $true)` - here the third parameter corresponds to read-only, so why are you using `$true`? – BigBen Jan 19 '22 at 17:55
  • Hi @BigBen - I've also used $false in the third parameter but it doesn't make a difference, it's still read-only. – CrudeOperator Jan 19 '22 at 17:57
  • 1
    The 7th parameter is *IgnoreReadOnlyRecommended*... I'd use `$true` for that as well. (and `$false` for the 3rd parameter). – BigBen Jan 19 '22 at 17:58
  • @BigBen - do you know how I declare parameters 4-6? I've tried just with '$null' but that throws an error: "The object invoked has disconnected from its clients. (Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))" – CrudeOperator Jan 19 '22 at 18:04
  • 3
    Try using `[Type]::Missing`. – BigBen Jan 19 '22 at 18:11
  • For anyone who comes along later, the details of the third and seventh parameter can be found at https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbooks.Open. Have a nice day. – Daniel 'Dang' Griffith Nov 21 '22 at 21:31

1 Answers1

3

For Workbooks.Open:

  • The 3rd parameter is ReadOnly and should be $false.
  • The 7th parameter is IgnoreReadOnlyRecommended and should be $true.
  • Use [Type]::Missing for the 4th-6th parameters.
$workbook = $excel.Workbooks.Open($FilePath, $null, $false, [Type]::Missing, [Type]::Missing, [Type]::Missing, $true)
BigBen
  • 46,229
  • 7
  • 24
  • 40