4

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
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Glowie
  • 2,271
  • 21
  • 60
  • 104
  • What version of MS Excel are you creating? *Freeze top row* only came along with Excel 2007 I think ... – Our Man in Bananas May 20 '15 at 18:24
  • possible duplicate of [How can I programmatically freeze the top row of an Excel worksheet in Excel 2007 VBA?](http://stackoverflow.com/questions/3232920/how-can-i-programmatically-freeze-the-top-row-of-an-excel-worksheet-in-excel-200) – Our Man in Bananas May 20 '15 at 18:25
  • see [SO: 3232920 how-can-i-programmatically-freeze-the-top-row-of-an-excel-worksheet](http://stackoverflow.com/questions/3232920/how-can-i-programmatically-freeze-the-top-row-of-an-excel-worksheet-in-excel-200) – Our Man in Bananas May 20 '15 at 18:26
  • @OurManInBananas I am trying that solution and it's not working ..... Using Excel 2007 – Glowie May 20 '15 at 18:28
  • 1
    take a look at [http://www.ozgrid.com/forum/showthread.php?t=19692](http://www.ozgrid.com/forum/showthread.php?t=19692) – Our Man in Bananas May 20 '15 at 18:36

3 Answers3

9

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

$excel.Rows.Item("2:2").Select()
$excel.ActiveWindow.FreezePanes = $true
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
1

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()
hostyd
  • 11
  • 1
0
$excel.Application.ActiveWindow.SplitRow = 1
$excel.Application.ActiveWindow.FreezePanes = $true
Surgut
  • 1