PowerShell code creates Excel.
I am trying to freeze top row:
$excel = New-Object -Com Excel.Application
$excel.Visible = $True
$wb = $Excel.Workbooks.Add()
$ws = $wb.Worksheets.Add()
$ws.Activate()
$ws.Select()
$excel.Rows.Item("1:1").Select()
$excel.ActiveWindow.FreezePanes = $true
Instead of freezing top row, it freezes center of rows and center of columns, i.e.
UPDATE
Solution in the duplicate post does not work, i.e.
$excel.Rows("1:1").Select()
$excel.ActiveWindow.FreezePanes = $true
gives the following error:
Method invocation failed because [System.__ComObject] does not contain a method named 'Rows'.
At D:\Script\upgrades.ps1:231 char:5
+ $excel.Rows("1:1").Select()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (Rows:String) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound
To freeze the top row you need to select the second row:
$excel.Rows.Item("2:2").Select()
$excel.ActiveWindow.FreezePanes = $true
If you have multiple sheets and need to freeze the top row of each sheet:
$colSheets = ($Sheet1, $Sheet2, $Sheet3, $Sheet4, $Sheet5)
foreach ($page in $colSheets){
$page.Select()
$page.application.activewindow.splitcolumn = 0
$page.application.activewindow.splitrow = 1
$page.application.activewindow.freezepanes = $true
}
#After you are done, re-select first sheet (optional)
$Sheet1.Select()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With