Page 1 of 1

Replace in excel

Posted: 20 Dec 2018, 04:39
by 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)

Re: Replace in excel

Posted: 21 Dec 2018, 01:46
by blue83
anyone?

Re: Replace in excel

Posted: 21 Dec 2018, 03:45
by rommmcek
Try XL.Range("B1").Value:="ok2" or XL.Selection.Value := "ok2"

Re: Replace in excel

Posted: 21 Dec 2018, 10:02
by Datapoint
blue83 wrote:
20 Dec 2018, 04:39
"" 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
blue83 wrote:
20 Dec 2018, 04:39
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)

Re: Replace in excel

Posted: 21 Dec 2018, 11:01
by sinkfaze
Datapoint wrote:
21 Dec 2018, 10:02

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.

Re: Replace in excel

Posted: 21 Dec 2018, 11:09
by Datapoint
sinkfaze wrote:
21 Dec 2018, 11:01
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.

Re: Replace in excel

Posted: 04 Jan 2019, 08:13
by blue83
Thank you to all. My biggest issue XL.DisplayAlerts := 0 is resolved.

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