search and replace values in excel from comma separated text file

PowerShell Help
1

I have a text file, which has

Server Name, IP Address, Comments
ABCserver1, 1.1.1.1, remote web server
DEFserver2, 2.2.2.2, remote app server
XYZserver3, 3.3.3.3, remote api server

I have a excel file which has Server Name, IP Address, Protocol, Port.
Server1, x.x.x.x, TCP, 80
Server2, x.x.x.x, TCP, 80
Server3, x.x.x.x, TCP, 80

My script search for the excel spread sheet for a array that I defined [Server1,Server2,Server3], once the first value matches in excel sheet it replaces all values of Server1 with first value in text file i.e: ABCServer1 so my new excel sheet looks this:
ABCserver1, x.x.x.x, TCP, 80
DEFserver2, x.x.x.x, TCP, 80
XYZserver3, x.x.x.x, TCP, 80

What I’m trying to do is that, it will also copy the ip address in next column meaning replace the first two columns, with first column as server name and second column with IP address and ignore anything in text file after second comma
I can get the script to replace the server name but unable to copy the IP address in next column. So this is what script will do if runs correctly:

Before script ran on excel sheet
Server1, x.x.x.x, TCP, 80
Server2, x.x.x.x, TCP, 80
Server3, x.x.x.x, TCP, 80

After
ABCserver1, 1.1.1.1, TCP, 80
DEFserver2, 2.2.2.2, TCP, 80
XYZserver3, 3.3.3.3, TCP, 80
Any ideas how I can replace the IP address as well in next column when it find the first value?

script:

$text = "Server1","Server2","Server3"
$replace=$replace = get-content C:\script\test.txt | foreach{ ($_.split(","))[0]}
$File = "C:\script\test.xlsx"
$now = [datetime]::now.ToString("yyyy-MM-dd")
#$now = get-date -Format "MM-dd-yyyy_hh:mm:ss"
copy-Item C:\script\test.xlsx test_$now.xlsx


# Setup Excel, open $File and set the the first worksheet
$i=0
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)
$Range = $Worksheet.UsedRange

Foreach($SearchString in $text){

if ($Range.find("$SearchString")) {
    $Range.replace($SearchString,$replace[$i])
    } 
else {$i++}

}

$WorkBook.Save()
$WorkBook.Close()
[void]$excel.quit()
2

When you start coding in Excel, it’s VBA, and it’s rather brutal as it’s a very COM interface that is really old. I believe this what you are trying to do, but if you are trying to figure something out search for VBA code or avoid Excel like the plague:

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$workbook = $Excel.Workbooks.Add()
$Worksheet = $Workbook.Worksheets.Item(1)

$range = $Worksheet.Range("A1")
$range.Cells.Value2 = "Test"
$range.Next.Value2 ="AnotherTest"
3

take a look at Doug Finke’s Import-excel module