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)
Replace in excel
Re: Replace in excel
anyone?
Re: Replace in excel
Try XL.Range("B1").Value:="ok2" or XL.Selection.Value := "ok2"
Re: Replace in excel
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
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
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.Datapoint wrote: ↑21 Dec 2018, 10:02Code: 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
Code: Select all
xlPart := 2
xlWhole := 1
xlByColumns := 2
xlByRows := 1
XL.DisplayAlerts := 0
XL.Selection.Replace("abc123", "ok2", xlPart, xlByRows)
XL.DisplayAlerts := 1
Re: Replace in excel
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:
- https://docs.microsoft.com/en-us/office ... ce#remarksThe 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.
in this case yes, but not always., and our standard boolean assignments are sufficient for communicating back through Office products.
Re: Replace in excel
Thank you to all. My biggest issue XL.DisplayAlerts := 0 is resolved.
Datapoint yes you are correct, I meant on a string.
Datapoint yes you are correct, I meant on a string.
Who is online
Users browsing this forum: No registered users and 160 guests