Return another value from same row

Welcome Forums General PowerShell Q&A Return another value from same row

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
7 months, 3 weeks ago.

  • Author
    Posts
  • #96696

    Participant
    Points: 45
    Rank: Member

    Hey Guys. Can someone help me?
    I am wanting to return a value from the same row but am unsure how it's done. I can get the row number but then I am stuck.

    Can someone point me in the right direction of what the next step would be.

    $File = "C:\Temp\DemoData.xlsx"
    
    $hostname = 'hostnameHere'
    
    #Serial in 'V1'
    #End date in 'H1'
    
    # Setup Excel, open $File and select the 2nd worksheet
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $false
    $Workbook = $Excel.workbooks.open($file)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(2)
    
    
    $Range = $Worksheet.Range("V1").EntireColumn
    $Cell = $Range.find($hostname)
    
    $RowNumber = $Cell.row
    
    $RowNumber
    
  • #96710

    Participant
    Points: 1
    Rank: Member

    Something like this?

    $worksheet=$objExcel.Worksheets.Item("Sheet1")
    $Range = $Worksheet.Range("V1").EntireColumn.find($hostname)
    $WorkSheet.Cells.Item($range.row,8).value2
    • #96726

      Participant
      Points: 1
      Rank: Member
      $File = "C:\Temp\DemoData.xlsx"
      
      $hostname = 'hostnameHere'
      
      #Serial in 'V1'
      #End date in 'H1'
      
      # Setup Excel, open $File and select the 2nd worksheet
      $Excel = New-Object -ComObject Excel.Application
      $Excel.visible = $false
      $Workbook = $Excel.workbooks.open($file)
      $Worksheets = $Workbooks.worksheets
      $Worksheet = $Workbook.Worksheets.Item(2)
      
      
      $Range = $Worksheet.Range("V1").EntireColumn
      $Cell = $Range.find($hostname)
      $RowNumber = $Cell.row
      
      #H = 8
      $Worksheet.Cells.Item($RowNumber, 8).text
      
    • #96866

      Participant
      Points: 45
      Rank: Member

      Thanks guys, my problem was that I wasn't giving it a number but the column letter like I was when searching the range.

      I'm still getting an error when it runs but it still works..

      Exception from HRESULT: 0x800A03EC
      At C:\Users\USername\OneDrive\Scripts\Lookup-LeaseShedule.ps1:22 char:2
      +     $EndDate = $Worksheet.Cells.Item($CellData.row, 8).text
      +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          + CategoryInfo          : OperationStopped: (:) [], COMException
          + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
      
      cls
      $File = "C:\Temp\DemoData.xlsx"
      
      $OU = OUpath
      
      #Get computer name and info field for each computer in $OU
      $Computers = Get-ADComputer -Filter * -SearchBase $OU -Properties Name,Description -Server domain | Select-Object Name,Description
      
      # Setup Excel, open $File and select the 2nd worksheet
      $Excel = New-Object -ComObject Excel.Application
      $Excel.visible = $false
      $Workbook = $Excel.workbooks.open($file)
      $Worksheets = $Workbooks.worksheets
      $Worksheet = $Workbook.Worksheets.Item(2)
      
      $Range = $Worksheet.Range("V1").EntireColumn
      
      Foreach ($Computer in $Computers) {
      	$CellData = $Range.find($Computer.name)
      	$EndDate = $Worksheet.Cells.Item($CellData.row, 8).text
      	$Description = $Computer.Description
      	Get-ADComputer $Computer.name -Properties Name,Description | Set-ADComputer -Description "Lease Expiry: $EndDate - $Description"
      }
      
    • #96899

      Participant
      Points: 1
      Rank: Member
      EndDate = $Worksheet.Cells.Item($CellData.row, 8).text

      You need to check if $CellData is filled.
      If the row is not found. then this line will fail

    • #97068

      Participant
      Points: 45
      Rank: Member

      Not sure how I missed that one..
      thanks.

The topic ‘Return another value from same row’ is closed to new replies.