Replace in excel

[blue83]

Hi,

I need help, I have found this code bellow and if there is a string (even in some sentence) in excel sheet everything works fine and code will do its work.

My problem is when string is not there (alone or in sentence) excel will display error message "Can not found and replace".

How can I avoid this message.

Code is:

VT_False := ComObj(0xB,0)
XL.Selection.Replace("" abc123 "", "ok2", 2, 1, VT_False, VT_False, VT_False)

[blue83]

anyone?

[rommmcek]

Try XL.Range("B1").Value:="ok2" or XL.Selection.Value := "ok2"

[Datapoint]

"" abc123 ""

Is that a variable? Did you mean for it to be a string? "abc123"

Code: Select all

vbFalse := ComObject(0xB, 0)
vbTrue := ComObject(0xB, -1)
xlPart := 2
xlWhole := 1
xlByColumns := 2
xlByRows := 1
XL.DisplayAlerts := vbFalse
XL.Selection.Replace("abc123", "ok2", xlPart, xlByRows, vbFalse, vbFalse, vbFalse)
XL.DisplayAlerts := vbTrue

My problem is when string is not there (alone or in sentence) excel will display error message "Can not found and replace".

Maybe do this first: XL.DisplayAlerts := vbFalse. https://docs.microsoft.com/en-us/office ... playAlerts
I suspect that try/catch would not catch that particular error because it is an excel error, not a COM error. (but I didn't test it out)

[sinkfaze]

Code: Select all

vbFalse := ComObject(0xB, 0)
vbTrue := ComObject(0xB, -1)
xlPart := 2
xlWhole := 1
xlByColumns := 2
xlByRows := 1
XL.DisplayAlerts := vbFalse
XL.Selection.Replace("abc123", "ok2", xlPart, xlByRows, vbFalse, vbFalse, vbFalse)
XL.DisplayAlerts := vbTrue

Just FYI, we're not required to fill in optional parameters in COM any longer, and our standard boolean assignments are sufficient for communicating back through Office products.

Code: Select all

xlPart := 2
xlWhole := 1
xlByColumns := 2
xlByRows := 1
XL.DisplayAlerts := 0
XL.Selection.Replace("abc123", "ok2", xlPart, xlByRows)
XL.DisplayAlerts := 1

And you are correct, disabling displayAlerts will bypass the warning.

[Datapoint]

Just FYI, we're not required to fill in optional parameters in COM any longer

It's not because I want to fill unused params. I am aware that ComObjMissing() is deprecated. It's to make sure settings from previous searches are not used:

The settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don?t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

- https://docs.microsoft.com/en-us/office ... ce#remarks

, and our standard boolean assignments are sufficient for communicating back through Office products.

in this case yes, but not always.

[blue83]

Thank you to all. My biggest issue XL.DisplayAlerts := 0 is resolved.

Datapoint yes you are correct, I meant on a string.