Search code examples
excelpowershellfindrow

Looking to read user in put and find string on column B of excel sheet - powershell

I am new to Powershell and I have an excel sheet that contains a list of subnets in column E for different store numbers 1 - 2500, store numbers are held in column B.

I want to read the store number from user and output the subnet from COLUMN E to a variable.

This is what I have so far..

$FilePath = "C:\temp\IP.xlsx"
$SheetName = "STORE SUBNET MASK"
$Excel = New-Object -ComObject Excel.Application
$WorkBook = $Excel.Workbooks.Open($FilePath)
$WorkSheet = $WorkBook.sheets.item($SheetName)
$num = Read-Host "Store number"


$Range = $Worksheet.Range("B1").EntireColumn
$Search = $Range.find($num)        
$Search.value() = "2"    


$WorkBook.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

What I am hoping to achieve is user inputs for example store 10 .. powershell checks column B for 10 and assigns what is in column E for that row to a variable.

Solution

  • Remove your code $Search.value() = "2" since it doesn't do anything.

    Place Write-Host $search.offset(0,3).Text after $Search = $Range.find($num)