Hi Scripting Guy:
I was able to write a script based on all the examples in your blogs, but now I'm stuck:

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. Server Name, and IP Addreess can repeat itself more than once in entire spreadsheet at any location: 

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 spreadsheet for a array that I have defined [Server1,Server2,Server3,...], once the first value matches in spreadsheet 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 also want is, when I replaces the "Server1" value in spreadsheet, it will also replace the IP Address in next column. This is where I'm stuck I can replace the value of Server Name, but I can't replace the IP address for text file. Remember "Server1" can be found at any place in spreadsheet but it will always have IP address column next to it.

So this is what script will do if runs correctly:

1) Read text file:
ABCserver1, 1.1.1.1, remote web server
DEFserver2, 2.2.2.2, remote app server
XYZserver3, 3.3.3.3, remote api server
...
...
...


2) Before script run on excel sheet

Server1, x.x.x.x, TCP, 80
Server2, x.x.x.x, TCP, 80
Server3, x.x.x.x, TCP, 80
...
...


3) After script run on excel sheet

ABCserver1, 1.1.1.1, TCP, 80
DEFserver2, 2.2.2.2, TCP, 80
XYZserver3, 3.3.3.3, TCP, 80
...
...


If you will see my script is able to replace "Server Name" in entire spreadsheet, but can't copy or replace the corespondent IP address in next column. 


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()



Really appreciate your help
Thanks

This is a duplicate of this: https://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/775810e7-f956-4e47-82a4-51ace01c07b5/#775810e7-f956-4e47-82a4-51ace01c07b5

And this: https://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/cef223c7-21dd-42d0-a674-482743586b0e/#42d69786-0d7f-4b92-b19d-e26101776210

As before you need to find the cell and then choose the cell next to it by adding one to the column of the cell address.

This is the pattern you need too use:

$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

$test=Import-Csv C:\script\test.txt -header search,replace
Foreach($item in $test){
    if($cell=$Worksheet.UsedRange.find($item.search)){
       $cell.Text=$item.replace
    }
}

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

This  is how it can be done but it would be better too fix your file design.

$servers='Server1','Server2','Server3'
$file='C:\scripts\test.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook=$Excel.workbooks.open($file)

$i=0
$test=Import-Csv C:\script\test.txt -header server,replace, description
Foreach($server in $servers){
    if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
       $cell.Text=$test[$i].server
       $workbook.Worksheets[1].Cells($cell.Row,$cell.Column+1)=$test[$i].relace
       $i++
    }
}

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

I just ran it and this is msg I'm getting:

You cannot call a method on a null-valued expression.
At C:\Users\xyx\AppData\Local\Temp\e621b511-f5da-4180-b542-31076315033d.ps1:10 char:8
+     if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 

• Marked as answer by jawano 9 hours 47 minutes ago

Sorry - missed one:

$servers='Server1','Server2','Server3'
$file='C:\scripts\test.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook=$Excel.workbooks.open($file)

$i=0
$test=Import-Csv C:\script\test.txt -header server,replace, description
Foreach($server in $servers){
    if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
       $cell.Value2=$test[$i].server
       $workbook.Worksheets[1].Cells($cell.Row,$cell.Column+1)=$test[$i].replace
       $i++
    }
}

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

pretty much getting same error, i copy and pasted as it is:

You cannot call a method on a null-valued expression.
At C:\Users\xxxx\AppData\Local\Temp\d6fc1e64-adaa-40f0-b211-5745b8456381.ps1:10 char:8
+     if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 

Works perfectly for me.  You must have broken something when copying.

copying pasting exactly I have, did I miss anything?

$servers='server1','server2',server3'
$file='C:\script\test.xlsx'
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook=$Excel.workbooks.open($file)

$i=0
$test=Import-Csv C:\script\test.txt -header server,replace, description
Foreach($server in $servers){
    if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
       $cell.Value2=$test[$i].server
       $workbook.Worksheets[1].Cells($cell.Row,$cell.Column+1)=$test[$i].relace
       $i++
    }
}

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

Can't help you with that.  The script works.  Perhaps your spreadsheet is damaged.

I recreated my text file and excel file:

this is how my text file looks:

REM88888SQL301,10.1.1.1,Remote SQL Server
REM88888SQL301A,10.1.1.2,Remote SQL Server A
REM88888SQL301B,10.1.1.3,Remote SQL Server B

This is how my new excel file looks

server1	x.x.x.x	TCP	80
server2	x.x.x.x	TCP	80
server3	x.x.x.x	TCP	80

When I changed $workbook.Worksheets[1] to $workbook.Worksheets.Item(1) it works. Can I make that change, becase then result is not same as it won't replaces the IP address. It will change server name but not IP thats how my excel looks after running script:

REM88888SQL301	x.x.x.x	TCP	80
REM88888SQL301A	x.x.x.x	TCP	80
REM88888SQL301B	x.x.x.x	TCP	80

As you can see x.x.x.x was not replaced with IP of the corespondent server

Excel 2007?  It may require the Item(1) setting.  2013 doesn't.

ahh i'm using Office 2010,

so one thing that is not working is that it is not replacing IP address:

Here is the code that I'm running when you run it does it change the IP address? 

I changed the file names and corrected path for my pc:

$servers='server1','server2','server3'

$file='C:\script\test2.xlsx'

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook=$Excel.workbooks.open($file)

$i=0
$test=Import-Csv C:\script\test2.txt -header server,replace, description

Foreach($server in $servers){
    if($cell=$workbook.Worksheets.Item(1).UsedRange.find($server)){
       $cell.Value2=$test[$i].server
        $workbook.Worksheets.Item(1).Cells($cell.Row,$cell.Column+1)=$test[$i].replace
       $i++
    }
}

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

As I posted, it works fine for me but I do not have 2010 to test it on.

I think it is failing because i get this error, any workaround  or calling the method differently? many thanks..

Method invocation failed because [System.__ComObject] does not contain a method named 'Cells'.
At C:\script\12.ps1:20 char:9
+         $workbook.Worksheets.Item(1).Cells($cell.Row,$cell.Column+1)=$test[$i].r ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Cells:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
 

I just ran it and this is msg I'm getting:

You cannot call a method on a null-valued expression.
At C:\Users\xyx\AppData\Local\Temp\e621b511-f5da-4180-b542-31076315033d.ps1:10 char:8
+     if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 

• Marked as answer by jawano Tuesday, April 28, 2015 9:21 PM

I just ran it and this is msg I'm getting:

You cannot call a method on a null-valued expression.
At C:\Users\xyx\AppData\Local\Temp\e621b511-f5da-4180-b542-31076315033d.ps1:10 char:8
+     if($cell=$workbook.Worksheets[1].UsedRange.find($server)){
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
 

• Marked as answer by jawano Tuesday, April 28, 2015 9:21 PM

Hey, thanks for helping me out. I checked online and I had to call Cells this way:

$Workbook.Worksheets.Item(1).Cells.item($cell.Row,$cell.Column+1)=$test[$i].replace

one problem I'm having is if the $server value repeats itself in excel sheet again it doesn't replace all values for lets see server 1:

so thats how my excel sheet is:

server1	x	TCP	80
server2	x	TCP	80
server3	x	TCP	80
server1	x	TCP	80

when I run the script thats how results looks:

server1	x	TCP	80
REM88888SQL301A	10.1.1.2	TCP	80
REM88888SQL301B	10.1.1.3	TCP	80
REM88888SQL301	10.1.1.1	TCP	80

It skipped the line one on excel and changed value in line 4. As I mentioned above this can repeat $server can repeat itself anywhere in spreadsheet in multiple columns.

What can we do so it will replace all server name and IP address?

Thanks

So now you re changing the rules.

To work with tha tyou have to loop on find until it fails to find then move to the next item.  You need a loop inside the loop.

Instead of "if" tis might work:

 while($cell=$workbook.Worksheets[1].UsedRange.find($server)){
       $cell.Value2=$test[$i].server
       $workbook.Worksheets[1].Cells($cell.Row,$cell.Column+1)=$test[$i].replace
    }
    $i++

no i'm not changing rule, sorry if u feel that way but I stated in original problem

"What I also want is, when I replaces the "Server1" value in spreadsheet, it will also replace the IP Address in next column. This is where I'm stuck I can replace the value of Server Name, but I can't replace the IP address for text file. Remember "Server1" can be found at any place in spreadsheet but it will always have IP address column next to it."

I will give a shot many thanks.

That works, thanks a lot.

That works, thanks a lot.

I am glad you sorted it.  Good luck.

I'm running into a strange issue here.

In script I have search strings:

$servers="MMMSQLServer","MMMSQLServerA","MMMSQLServerB"

Same search string is in excel file, see screen print:

When I run script it finds the first search string MMMSQLServer and replace all MMMSQLServer/MMMSQLServerA/MMMSQLServerB with only the first value in text file. Where it supposed to replace

MMMSQLServer = HKzzzSQL301,10.10.10.10

MMMSQLServerA = HKzzzSQL301A,20.20.20.20

MMMSQLServerB = HKzzzSQL301B,30.30.30.30

here is the screen shot of after the script:

Here is my script I didn't change much just change file names and path:

$servers="MMMSQLServer","MMMSQLServerA","MMMSQLServerB"
$file='C:\script\DA_Port_Request.xlsx'

$now = [datetime]::now.ToString("yyyy-MM-dd")
Copy-Item C:\script\DA_Port_Request.xlsx "C:\script\DA_Port_Request.$now.xlsx"

# Setup Excel, open $File and set the the first worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook=$Excel.workbooks.open($file)

$i=0
$test=Import-Csv C:\script\ServerList.txt -header server,replace, description

$test

Foreach($server in $servers){
    while($cell=$Workbook.Worksheets.Item(1).UsedRange.find($server)){
       $cell.Value2=$test[$i].server 
  	   
	   $cell.Value2
	   
        $Workbook.Worksheets.Item(1).Cells.item($cell.Row,$cell.Column+1)=$test[$i].replace
           } $i++
}

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

Thanks

It looks like the find is doing partial cell matching, try find($server,,1) instead, this should do a whole cell match only.

It is useful to look up the commands and read how they work.

https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

I should also note that PowerShell requires all values.  You cannot skip arguments with commas as in VBA.  The xlValues enum is not "1".

To replace multiple values you have to use Find in a loop

looks like I also missed a comma from what I thought would help, was working from memory.  Try this for the find instead, it appears to work

find($server,[Type]::Missing,[Type]::Missing,1)

• Marked as answer by jawano 16 hours 59 minutes ago

Yes that works thanks a lot. I will read on what you sent.

Thanks again.

looks like I also missed a comma from what I thought would help, was working from memory.  Try this for the find instead, it appears to work

find($server,[Type]::Missing,[Type]::Missing,1)

• Marked as answer by jawano Thursday, April 30, 2015 2:09 PM