3

I have a document set to request that user open a read only version(Option "Read-only Recommended"). I would like to open the excel document without read on only in powershell (decline the prompt asking to open "Read Only"). Here is my current code.

$dir = "\\file_path\*"
$latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$latest.name

$excelObj = New-Object -ComObject Excel.Application
$excelObj.Visible = $True
$excelObj.DisplayAlerts = $False


$workBook = $excelObj.Workbooks.Open($latest)

How do I ignore the read only prompt and open the full version?

TigermanSD
  • 77
  • 1
  • 2
  • 7

2 Answers2

3

There should be a IgnoreReadOnlyRecommended argument that you can supply in the workbook open method:

$workBook = $excelObj.Workbooks.Open($latest,,,,,,$True,,,,,,,)

Workbooks.Open Method (MSDN)

Edit

Based on comments below, it appears that there is a bug preventing this method from working when the $null parameters are supplied. Thanks to this answer on another question it appears there may be a way around this:

1st, this function is required:

Function Invoke-NamedParameter {
    [CmdletBinding(DefaultParameterSetName = "Named")]
    param(
        [Parameter(ParameterSetName = "Named", Position = 0, Mandatory = $true)]
        [Parameter(ParameterSetName = "Positional", Position = 0, Mandatory = $true)]
        [ValidateNotNull()]
        [System.Object]$Object
        ,
        [Parameter(ParameterSetName = "Named", Position = 1, Mandatory = $true)]
        [Parameter(ParameterSetName = "Positional", Position = 1, Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [String]$Method
        ,
        [Parameter(ParameterSetName = "Named", Position = 2, Mandatory = $true)]
        [ValidateNotNull()]
        [Hashtable]$Parameter
        ,
        [Parameter(ParameterSetName = "Positional")]
        [Object[]]$Argument
    )

    end {  ## Just being explicit that this does not support pipelines
        if ($PSCmdlet.ParameterSetName -eq "Named") {
            ## Invoke method with parameter names
            ## Note: It is ok to use a hashtable here because the keys (parameter names) and values (args)
            ## will be output in the same order.  We don't need to worry about the order so long as
            ## all parameters have names
            $Object.GetType().InvokeMember($Method, [System.Reflection.BindingFlags]::InvokeMethod,
                $null,  ## Binder
                $Object,  ## Target
                ([Object[]]($Parameter.Values)),  ## Args
                $null,  ## Modifiers
                $null,  ## Culture
                ([String[]]($Parameter.Keys))  ## NamedParameters
            )
        } else {
            ## Invoke method without parameter names
            $Object.GetType().InvokeMember($Method, [System.Reflection.BindingFlags]::InvokeMethod,
                $null,  ## Binder
                $Object,  ## Target
                $Argument,  ## Args
                $null,  ## Modifiers
                $null,  ## Culture
                $null  ## NamedParameters
            )
        }
    }
}

Which would suggest the Workbooks.Open() method could be called like so:

$workBook = Invoke-NamedParameter $excelObj "Workbooks.Open" @{"FileName"=$latest;"IgnoreReadOnlyRecommended"=$True}
Community
  • 1
  • 1
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • 1
    When did the first command become valid syntax? Have I slept through the class? :-) Haven't seen it before and it throws error in PowerShell 5.0 – Frode F. May 11 '16 at 19:57
  • @FrodeF. I'm just about to edit that bit out - I'm not a proper PowerShell user, just a fan of Excel Interop but I've seen arguments passed in this fashion before so figured it might work. After some testing I realise that isn't the case! – SierraOscar May 11 '16 at 19:58
  • 1
    `-Filename ....` etc. is for cmdlets and functions. Methods in .NET and COM-objects uses standard c# syntax `Open("foo","bar",$null,$null....)`. You need to provide `$null` for unused optional parameters. However, when I tested this I met a bug in the Excel interop that was recently mentioned here on StackOverflow where the `Open()` method fails after 3-4 parameters.. http://stackoverflow.com/questions/35452262/error-opening-excel-in-powershell/35508942 – Frode F. May 11 '16 at 20:19
  • @FrodeF. good to know, at risk of going off-topic is there no way to call arguments out-of-sequence by specifying the argument's name? (In VB for example you would use `argName:=value` syntax) – SierraOscar May 11 '16 at 20:23
  • Unfortunately there's not any simple way of using named parameters for .NET methods in PowerShell. You can use the `InvokeMember()`-method in a `TypeInfo`-object for the class. See this Stackoverflow question for explanation and a function to simplify it: http://stackoverflow.com/questions/5544844/how-to-call-a-complex-com-method-from-powershell – Frode F. May 11 '16 at 20:28
  • That's a nice little gem to refer to! Not often you post an answer to a question and learn something new yourself in the process. Hats off to you sir :) – SierraOscar May 11 '16 at 20:30
  • No problem. Here's a link to an old uservoice (previously Connect)-idea for named/optional-parameter support in methods. http://windowsserver.uservoice.com/forums/301869-powershell/suggestions/11088096-powershell-doesn-t-support-calling-net-methods-cr – Frode F. May 11 '16 at 20:37
  • 1
    I see, that makes a lot of sense now - surprised that this hasn't been sorted by now though... – SierraOscar May 11 '16 at 20:46
3

If your looking to just open the file for reading and ignore the prompt then this works:

$workBook = $excelObj.Workbooks.Open($latest,$null,$true)

The 3rd argument denotes true to open read-only. This approach does not appear to be subject to the aforementioned bug!

Phil
  • 31
  • 1