Excel template

PowerShell Help
1

Hello,

The code below works for xlsx files but not templates, xltx. I believe its how excel treats templates when opening. How can I open a folder with many templates files in it and make the data additions below?

Thank you

$excel_file_path = '\\Network\public\Home'
$excelFiles = Get-ChildItem -Path $excel_file_path -include *.xltx, *.xlsx, *.xlsm, *.xls -recurse

# Create the Excel application object
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false #Do not open individual windows

foreach($excelFiles in $excelFiles)
{
## Instantiate the COM object
$objExcel = New-Object -ComObject Excel.Application
$ExcelWorkBook = $objExcel.Workbooks.Open($excelFiles)
$ExcelWorkSheet = $objExcel.WorkSheets.item("Order_Optional_Fields")
$ExcelWorkSheet.activate()
## Find the first row where the first 7 columns are empty
$ExcelWorkSheet.Cells.Item(4,1) = '1234'
$ExcelWorkSheet.Cells.Item(4,2) = 'PRIORTY'
$ExcelWorkSheet.Cells.Item(4,3) = 'YES'

$ExcelWorkBook.Save($excelFiles.Fullname)
$objExcel.Workbooks.close()
}

$objExcel.Quit()

$wsh = New-Object -ComObject Wscript.Shell
2

Hi,

You’re right about the template being the problem. When you call the Open() method on a template, you need to set the Editable parameter to True. By default, it’s False when opening templates:

I struggled with finding how to pass the parameter but found a post on Stackoverflow that has a implemented a clever way of using named parameters.

Credit for Invoke-NamedParameter:
https://stackoverflow.com/questions/5544844/how-to-call-a-complex-com-method-from-powershell

I had to modify your code a bit to get it working, see comments:

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
            )
        }
    }
}

$excel_file_path = 'E:\temp\' #MB changed to local path for testing
$excelFiles = Get-ChildItem -Path $excel_file_path -include *.xltx -Recurse #MB changed for templates only

# Create the Excel application object
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false #Do not open individual windows

foreach($excelFile in $excelFiles) #MB corrected variable name
{
## Instantiate the COM object
$objExcel = New-Object -ComObject Excel.Application

#MB Use Invoke-NamedParameter to call the Open() method

$ExcelWorkBook = Invoke-NamedParameter $objExcel.Workbooks "Open" @{"FileName"=$excelFile.FullName;"Editable"=$true}

$ExcelWorkSheet = $objExcel.WorkSheets.item("Order_Optional_Fields")
$ExcelWorkSheet.activate()
## Find the first row where the first 7 columns are empty
$ExcelWorkSheet.Cells.Item(4,1) = '1234'
$ExcelWorkSheet.Cells.Item(4,2) = 'PRIORTY'
$ExcelWorkSheet.Cells.Item(4,3) = 'YES'

$ExcelWorkBook.Save() #MB Removed filename 
$objExcel.Workbooks.close()
}

$objExcel.Quit()

$wsh = New-Object -ComObject Wscript.Shell