Excel – Freeze the top row of a worksheet

excelpowershell

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.

enter image description here

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

Best Answer

To freeze the top row you need to select the second row:

$excel.Rows.Item("2:2").Select()
$excel.ActiveWindow.FreezePanes = $true
Related Topic