Function Reference

_Excel_RangeReplace

Finds and replaces matching strings in a range or worksheet

#include <Excel.au3>
_Excel_RangeReplace ( $oWorkbook, $vWorksheet, $vRange, $sSearch, $sReplace [, $iLookAt = $xlPart [, $bMatchcase = False]] )

Parameters

$oWorkbook Excel workbook object
$vWorksheet Name, index or worksheet object to be searched. If set to keyword Default the active sheet will be used
$vRange A range object, an A1 range or keyword Default to search all cells in the specified worksheet
$sSearch Search string
$sReplace Replace string
$iLookAt [optional] Specifies whether the search text must match as a whole or any part. Can be any of the XLLookAt enumeration (default = $xlPart)
$bMatchcase [optional] True = case sensitive, False = case insensitive (default = False)

Return Value

Success: the range object and sets @extended to 1 if cells have been changed.
Failure: 0 and sets @error.
@error: 1 - $oWorkbook is not an object or not a workbook object
2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code
3 - $sSearch is empty
4 - $vRange is invalid. @extended is set to the COM error code
5 - Error returned by the Replace method. @extended is set to the COM error code

Related

_Excel_RangeFind

Example

Example 1

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Replace content of cell A1 with another value
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example 1", "Press OK to modify data in cell 'A1'.")
_Excel_RangeReplace($oWorkbook, Default, "A1", 1, 3.37)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example 1", "Error replacing data the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example 1", "Data successfully replaced in cell 'A1'.")

Example 2

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Find all cells with text "long " and remove it (replace with "")
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example 1", "Press OK to modify data in cell 'G1'.")
_Excel_RangeReplace($oWorkbook, Default, Default, "long ", "")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example 2", "Error replacing data the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeReplace Example 2", "Remove text 'long '." & @CRLF & "Data successfully replaced.")