Dynamic Excel Range

Tagged: 

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Brian Clanton Brian Clanton 4 weeks, 1 day ago.

  • Author
    Posts
  • #73795
    Profile photo of Brian Clanton
    Brian Clanton
    Participant

    I have written a script that output to an Excel spreadsheet Groups from Active Directory, crate a header row based on those group names, and list users within that OU and then Place an 'X' in the column if that user is a member of that group.

    For formatting purposes, I want to put a Title at the top of the Excel Page and merge the cells of that same row based on the number of columns that are use, purpose being to perfectly center the title to the number of columns. The number of columns is going to be a dynamic number since the number of groups per OU will be a random number. My problem is that I don't know how to get the range of the worksheet without referencing a letter.

    I can get the last column number of an Excel column without an issue and assign it to a variable.

    $excel = New-Object -ComObject excel.application
    $excel.visible = $True
    $workbook = $excel.Workbooks.Add()
    $TPTechs= $workbook.Worksheets.Item(1)
    $TPTechs.Name = "XXXXXX HOsted User Info"
    
    $TPTechs.Cells.Item(1,1) = 'Title of Excel Workbook'
    ...
    ...
    ...
    ....
    $mainRng = $tptechs.usedrange.cells
    $ColCount = $mainRng.columns.count
    $xCol = $ColCount

    So now, how do I get 'X'? I don't know how to determine the Letter column and the Range Method doesn't seem to understand R1C1 notation.

    $MergeCells = $Tptechs.Range(A1,'X')
    $MergeCells.Merge()
  • #73862
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    This is more a VBA question than a PowerShell question, but if you know the number values of the cells, then you should be able use that to pass into Range similar to this.

    $MergeCells = $Tptechs.Range(Cells(2, 3), Cells(10, 4))
    • #73874
      Profile photo of Brian Clanton
      Brian Clanton
      Participant

      I tried that but PowerShell doesn't like that syntax and I can't find a working example of how to specify a range with Cell location as arguments.

      $excel = New-Object -ComObject excel.application
      $excel.visible = $True
      $workbook = $excel.Workbooks.Add()
      $TPTechs= $workbook.Worksheets.Item(1)
      $TPTechs.Name = "XXXXXX HOsted User Info"
      
      $TPTechs.Cells.Item(1,1) = 'Title of Excel Workbook'
      $MergeCells = $Tptechs.Range(Cells(1, 1), Cells(1, 4))

      OutPut

      [Localhost]C:\Users\bclanton\Google Drive\Code\Projects\Excel\Test-MergeCells.ps1
      At C:\Users\bclanton\Google Drive\Code\Projects\Excel\Test-MergeCells.ps1:14 char:30
      + $MergeCells = $Tptechs.Range(Cells(2, 3), Cells(10, 4))
      +                              ~
      Missing ')' in method call.
      At C:\Users\bclanton\Google Drive\Code\Projects\Excel\Test-MergeCells.ps1:14 char:30
      + $MergeCells = $Tptechs.Range(Cells(2, 3), Cells(10, 4))
      +                              ~~~~~
      Unexpected token 'Cells' in expression or statement.
      At C:\Users\bclanton\Google Drive\Code\Projects\Excel\Test-MergeCells.ps1:14 char:55
      + $MergeCells = $Tptechs.Range(Cells(2, 3), Cells(10, 4))
      +                                                       ~
      Unexpected token ')' in expression or statement.
          + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
          + FullyQualifiedErrorId : MissingEndParenthesisInMethodCall
  • #73879
    Profile photo of Curtis Smith
    Curtis Smith
    Participant
    $excel = New-Object -ComObject excel.application
    $excel.visible = $True
    $workbook = $excel.Workbooks.Add()
    $TPTechs= $workbook.Worksheets.Item(1)
    $TPTechs.Name = "XXXXXX HOsted User Info"
    
    $TPTechs.Cells.Item(1,1) = 'Title of Excel Workbook'
    $MergeCells = $Tptechs.Range($TPTechs.Cells(1, 1), $Tptechs.Cells(1, 4))
    $MergeCells.Merge()
    

You must be logged in to reply to this topic.