PowerShell Excel search replace issue with loop












0















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









share|improve this question





























    0















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









    share|improve this question



























      0












      0








      0








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









      share|improve this question
















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






      microsoft-excel powershell






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Apr 23 '15 at 6:02









      Karan

      49.2k1489160




      49.2k1489160










      asked Apr 23 '15 at 5:39









      dino110dino110

      111




      111






















          1 Answer
          1






          active

          oldest

          votes


















          0














          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!






          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "3"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f905017%2fpowershell-excel-search-replace-issue-with-loop%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            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!






            share|improve this answer




























              0














              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!






              share|improve this answer


























                0












                0








                0







                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!






                share|improve this answer













                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!







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Apr 24 '15 at 6:27









                Evan CottleEvan Cottle

                261




                261






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Super User!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f905017%2fpowershell-excel-search-replace-issue-with-loop%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    flock() on closed filehandle LOCK_FILE at /usr/bin/apt-mirror

                    Mangá

                    Eduardo VII do Reino Unido