none
PowerShell - Setting PrintTitleRows is not working. RRS feed

  • Question

  • I am trying to set the "Rows to repeat at the top" value with PowerShell.  It does not give me an error, but it doesn't set the value when I open the created spreadsheet.  I am using PowerShell v2 with Excel 2010.  Here is my sample code.

    $path = “c:\CSGReport\_Atest.xlsx”
    
    # Create the worksheet
    $excel = new-object -comobject Excel.Application
    $workbooks = $excel.Workbooks.Add()
    $worksheets = $workbooks.worksheets
    $worksheet = $worksheets.Item(1)
    
    # Set print title rows so these print out on every page.
    $excel.ActiveWorkbook.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
    
    
    # Row 1
    $worksheet.Cells.Item(1,1) = “My Title"
    $worksheet.Cells.Item(1,1).Font.Bold = $True
    $worksheet.Cells.Item(1,1).Font.Size = 18
    
    # Populate some test data
      for ($row=2; $row -le 75; $row++ ){
      	$worksheet.Cells.Item($row,1) = "Some Data $row"
      }
    
    # Save the file
    Remove-Item -Path $path -Force -ErrorAction SilentlyContinue
    $workbooks.SaveAs($path)
    $excel.quit()
    


    Monday, August 4, 2014 9:26 PM

Answers

  • Hi wildesdd,

    I’m not very familiar with PowerShell. According to my research, “$” is a reserved character in PowerShell. So I think you should use single quotes rather than double quotes.

    # Set print title rows so these print out on every page.
    $excel.ActiveWorkbook.ActiveSheet.PageSetup.PrintTitleRows = '$1:$1'

    Hope it will help.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by wildesdd Thursday, August 7, 2014 3:30 PM
    Thursday, August 7, 2014 2:00 AM
    Moderator

All replies

  • Hi,

    To set the value of a Cell with PowerShell,  I think you resort to Value2 property of range object. You could edit the code as followed.

    # Row 1
    $worksheet.Cells.Item(1,1).Value2 = “My Title"
    $worksheet.Cells.Item(1,1).Font.Bold = $True
    $worksheet.Cells.Item(1,1).Font.Size = 18
    
    # Populate some test data
      for ($row=2; $row -le 75; $row++ ){
      	$worksheet.Cells.Item($row,1).Value2 = "Some Data $row"
      }


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 5, 2014 8:41 AM
    Moderator
  • That still doesn't print the title line on each page.
    Tuesday, August 5, 2014 3:02 PM
  • Try this for first row and first column,
    
    $sheet.PageSetup.PrintTitleRows = [char]36+[char]49+[char]58+[char]36+[char]49
    
    $sheet.PageSetup.PrintTitleColumns = [char]36+[char]65+[char]58+[char]36+[char]65

    Thursday, August 7, 2014 1:29 AM
  • Hi wildesdd,

    I’m not very familiar with PowerShell. According to my research, “$” is a reserved character in PowerShell. So I think you should use single quotes rather than double quotes.

    # Set print title rows so these print out on every page.
    $excel.ActiveWorkbook.ActiveSheet.PageSetup.PrintTitleRows = '$1:$1'

    Hope it will help.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by wildesdd Thursday, August 7, 2014 3:30 PM
    Thursday, August 7, 2014 2:00 AM
    Moderator