Replace in excel

Get help with using AutoHotkey and its commands and hotkeys
blue83
Posts: 32
Joined: 11 Apr 2018, 06:38

Replace in excel

20 Dec 2018, 04:39

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
Posts: 32
Joined: 11 Apr 2018, 06:38

Re: Replace in excel

21 Dec 2018, 01:46

anyone?
rommmcek
Posts: 560
Joined: 15 Aug 2014, 15:18

Re: Replace in excel

21 Dec 2018, 03:45

Try XL.Range("B1").Value:="ok2" or XL.Selection.Value := "ok2"
User avatar
Datapoint
Posts: 66
Joined: 18 Mar 2018, 17:06

Re: Replace in excel

21 Dec 2018, 10:02

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)
User avatar
sinkfaze
Posts: 607
Joined: 01 Oct 2013, 08:01

Re: Replace in excel

21 Dec 2018, 11:01

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.
User avatar
Datapoint
Posts: 66
Joined: 18 Mar 2018, 17:06

Re: Replace in excel

21 Dec 2018, 11:09

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.
blue83
Posts: 32
Joined: 11 Apr 2018, 06:38

Re: Replace in excel

04 Jan 2019, 08:13

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

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

Return to “Ask For Help”

Who is online

Users browsing this forum: Bing [Bot], flyingDman, Google [Bot], WilburBr and 141 guests