Author Posts

March 22, 2018 at 2:53 am

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

March 22, 2018 at 9:03 am

Something like this?

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

March 22, 2018 at 10:17 am

$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

March 22, 2018 at 10:28 pm

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"
}

March 23, 2018 at 8:05 am

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

March 25, 2018 at 8:22 pm

Not sure how I missed that one..
thanks.