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