locked
PowerShell - Searching Excel and replacing text RRS feed

  • Question

  • Hi all,

    I am trying to figure out how to loop this script so that it replaces all the text that I am searching for in my Excel file.  I am able to get it to run but it only replaces the text in the first column and then ends. Any help is appreciated and thanks in advance.

    -Gaz

    $File = "D:\test.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)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)

    $SearchString = "TEMP# This is the value that I will be searching for

    $Range = $Worksheet.Range("A1").EntireColumn
    $Search = $Range.find($SearchString)

    $Search.value() = "ABSENT" # This is the value that i want to replace the text with.

    Tuesday, February 18, 2014 8:54 PM

Answers

  • Hi,

    You can use the FindNext and FindPrevious methods to repeat the search.

    $File = "c:\test\book2.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)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    
    $SearchString = "TEMP"  # This is the value that I will be searching for
    
    $Range = $Worksheet.Range("A1").EntireColumn
    
    
    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
    	$FirstAddress = $search.Address
    	do {
    		$Search.value() = "ABSENT"
    		$search = $Range.FindNext($search)
    	} while ( $search -ne $null -and $search.Address -ne $FirstAddress)
    }
    
    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()

    If you want to work on multiple columns, let's say "A1:B10": pls see the example below

    $Worksheet.Range("A1","B10")

    rgds,


    • Edited by Neothwin Wednesday, February 19, 2014 8:22 AM
    • Proposed as answer by Yan Li_ Thursday, February 20, 2014 3:39 AM
    • Marked as answer by Yan Li_ Friday, February 28, 2014 7:08 AM
    Wednesday, February 19, 2014 8:17 AM

All replies

  • Hi,

    You can use the FindNext and FindPrevious methods to repeat the search.

    $File = "c:\test\book2.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)
    $Worksheets = $Workbooks.worksheets
    $Worksheet = $Workbook.Worksheets.Item(1)
    
    $SearchString = "TEMP"  # This is the value that I will be searching for
    
    $Range = $Worksheet.Range("A1").EntireColumn
    
    
    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
    	$FirstAddress = $search.Address
    	do {
    		$Search.value() = "ABSENT"
    		$search = $Range.FindNext($search)
    	} while ( $search -ne $null -and $search.Address -ne $FirstAddress)
    }
    
    $WorkBook.Save()
    $WorkBook.Close()
    [void]$excel.quit()

    If you want to work on multiple columns, let's say "A1:B10": pls see the example below

    $Worksheet.Range("A1","B10")

    rgds,


    • Edited by Neothwin Wednesday, February 19, 2014 8:22 AM
    • Proposed as answer by Yan Li_ Thursday, February 20, 2014 3:39 AM
    • Marked as answer by Yan Li_ Friday, February 28, 2014 7:08 AM
    Wednesday, February 19, 2014 8:17 AM
  • Thank you all for taking the time to look at and answer my question.
    Friday, February 28, 2014 2:19 PM
  • Hi !

    Thank you for this, it's really helpfull.

    However, what if you want to remove a specifc word in that collum's cell ?

    I'm generating exchange reports and in some cases I get a value like : SMTP:JOHN.SMITH@AA.COM

    To push my reports cleaner, I'd like to remove the "SMTP:" word in front of the address. Your script remove the whole "SMTP:JOHN.SMITH@AA.COM" instead of only the "SMTP".

    Here's how mine is setup :

    $File = "c:\users\Rollina\Desktop\output-UserName.csv"

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

    $SearchString = "SMTP:"  # This is the value that I will be searching for

    $Range = $Worksheet.Range("D2").EntireColumn


    $Search = $Range.find($SearchString)
    if ($search -ne $null) {
        $FirstAddress = $search.Address
        do {
            $Search.value() = ""
            $search = $Range.FindNext($search)
        } while ( $search -ne $null -and $search.Address -ne $FirstAddress)
    }

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

    Thank you !

    Thursday, January 24, 2019 9:01 PM
  • Hello!

    But your code does the same. It clears the whole cell instead just "SMTP:".

    Wednesday, May 15, 2019 7:09 AM