Bdtyktl

What I'm doing here is to search excel sheet for a value, if value matches it will be replaced by first value in a given text file:

example:

search for value text find the first instance replace with first value in a given text file which is "read", search again for text find again replace with "read" until can't find "text", now search for second value in array "text1", search in excel sheet find it replace with second line in given text value, which is "read1" and so on.

I have the script that is working somewhat, issue is it will replace all array values with only first line of text file which is "read", it is not looping through the second line of text file.

Can someone please help.

code:

test.txt file read as:

read

read1

read2

$text = "text","text1","text2","text3"

$replace=get-content C:scripttest.txt

$File = "C:scripttest.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.Range("A1","Z10").EntireColumn



Foreach($SearchString in $text){

$Search = $Range.find($SearchString)



if ($search -ne $null){



$SearchString

$replace[$i]



$FirstAddress = $search.Address

do {

        $Search.value() = $replace[$i]

        $search = $Range.FindNext($search)



    } while ( $search -ne $null -and $search.Address -ne $FirstAddress )



$i++

}



 }

$WorkBook.Save()

$WorkBook.Close()

[void]$excel.quit()

I think the problem is that by default it's searching for "text" anywhere in the cell, which matches "text", "text1", "text2", etc. You could rearrange things so that "text" is the last one it searches for, but that's not ideal. Really, we need to get it to match the entire cell.

Thankfully there's a parameter in Range.Find() that allows us to do exactly that: LookAt (https://msdn.microsoft.com/en-us/library/office/ff839746.aspx).

Unfortunately it's a bit of a pain to access named parameters in COM objects in PowerShell, so the easier thing is to just specify the preceding parameters specifically:

$First = $Worksheet.Range("A1")    

$Search = $Range.find($SearchString,$First,-4163,1)

$First is A1, -4163 is the enum value for look in values, and 1 is the enum value for whole.

The PS-COM interop for Excel is real finicky about data types. Where I would normally expect to be able to pick out the first cell from the range and pass it to the function, it doesn't seem to like that, so I specified it manually in $First.

I looked up the enums and hard-coded them because that's another thing that's painful in PowerShell - hopefully someone might be able to suggest a nice way of doing that.

So the whole script ends up being:

$text = "text","text1","text2","text3"

$replace=get-content C:scripttest.txt

$File = "C:scripttest.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.Range("A1","Z10").EntireColumn

$First = $Worksheet.Range("A1")



Foreach($SearchString in $text){

$Search = $Range.find($SearchString,$First,-4163,1)



if ($search -ne $null){



$SearchString

$replace[$i]



$FirstAddress = $search.Address

do {

        $Search.value() = $replace[$i]

        $search = $Range.FindNext($search)



    } while ( $search -ne $null -and $search.Address -ne $FirstAddress )



$i++

}



 }

$WorkBook.Save()

$WorkBook.Close()

[void]$excel.quit()

It will remember the find settings for the FindNext() calls, so no need to change anything there.

Hope that helps!