Using RegExReplace for creating an Array Topic is solved

[Scr1pter]

Hello,

I would like to create an Array which consists of Excel cells.
When I'm in Excel, I simply would like to press a key which copies the current cell name.
Additionally, the copied cell name must be modified.
After that was done, the info should be sent to Notepad++.

An example:
Cell name: K4
The result should be: "K4", (with space at the end)

I already had partial success.

Code: Select all

F1::
MouseGetPos, xx, yy ; Get current mouse position
Sleep, 5
MouseMove 111, 153 ; Move cursor to cell name field
Sleep, 5
Send {lbutton} ; Click inside of field
Sleep, 20
Send ^c ; Copy cell name
Sleep, 5
Clipboard := RegExReplace(Clipboard,"m`r`n)^.*$","'$0',") ; Modify value -> here I need help! <-
Sleep, 5
Send {Esc} ; Exit field
Sleep, 5 
ControlSend, Scintilla1, ^v, ahk_class Notepad++ ; Send copied value to Notepad++
Sleep, 5
MouseMove, xx, yy, 0 ; Move mouse cursor to original position
return

I guess I don't need the m and n inside of the RegExReplace, because it's only a single line and only a single word.
Can you help, please?

P.S. I know COM is probably the better way, but it's too advanced for me. This method here would be fine for me, when it works...

Regards

[jeeswg]

- Using COM shouldn't be too difficult. Try a hello world example, look at a few example scripts, and try recording some macros in Excel (to discover more of the methods/properties that you can use).
- The key thing to be aware of is this:
oXl := Excel_Get() ;operate on an existing Excel window
oXl := ComObjCreate("Excel.Application") ;create a new Excel window
- To run the simple script below, you need the Excel_Get function, I've provided a link to it.

Code: Select all

;Excel_Get - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=31840

q:: ;Excel - get address of current cell
oXl := Excel_Get()
;vAddress := oXl.ActiveCell.Address ;e.g. $A$1
vAddress := oXl.ActiveCell.Address(0, 0) ;e.g. A1
oXl := ""
vAddress .= " " ;append a space
MsgBox, % vAddress
return

- Please explain what you're trying to do with the RegEx, it should be pretty straightforward to fix it. Cheers.

[Scr1pter]

- Please explain what you're trying to do with the RegEx, it should be pretty straightforward to fix it. Cheers.

Hi,

Thanks for your answer.
I would like to change the value (which is stored in the Clipboard)

K1
to
"K1",
(Of course it could also be A1, X14, Z500 etc.)

When I copy and paste the values (without modifying them), the result would be:
K1L1M1N1

If each of them was modified, the result would be:
"K1", "L1", "M1", "N1",
(This is how I would like to have it!)

As said, I copy 1 value, it gets pasted to an external editor, then I copy the next value and so on.

Regards

[jeeswg]

I wouldn't use RegEx:

Code: Select all

vOutput .= Chr(34) vAddress Chr(34) ", "
vOutput .= """" vAddress """, " ;equivalent to the line above

You could explain more clearly what you want via some example input and output, because whatever it is, it should probably be quite simple via COM.

[Scr1pter]

My aim is to have the possibility to quickly copy the cell name/cell number of the current cell inside of an Excel file.
Whenever I am on cell XYZ123 (doesn't matter which one), I want to press a key so that this cell name/number
gets stored as an Array element.
It can be stored in Notepad, in Notepad++, wherever.
(In my example I used Notepad++)

So when I do this for 5 cells (marking one at a time), I expect to have them in such a format:
"cell1", "cell2", "cell3", "cell4", "cell5"

Instead of:
cell1cell2cell3cell4cell5

I already found a work-around, which isn't that good, but still works:

Code: Select all

F1::
MouseGetPos, xx, yy ; Get current mouse position
Sleep, 5
Send {lbutton} ; Click inside of the cell
Sleep, 5
MouseMove 111, 153, 0 ; Move cursor to cell name field
Sleep, 5
Send {lbutton} ; Click inside of field
Sleep, 20
Send ^c ; Copy cell name
Sleep, 5
Send {Esc} ; Exit field
Sleep, 5 
ControlSend, Scintilla1, ", ahk_class Notepad++ ; Send " character to Notepad++
Sleep, 5
ControlSend, Scintilla1, ^v, ahk_class Notepad++ ; Send copied value to Notepad++
Sleep, 5
ControlSend, Scintilla1, ", ahk_class Notepad++ ; Send " character to Notepad++
Sleep, 5
ControlSend, Scintilla1, `, , ahk_class Notepad++ ; Send , to Notepad++
Sleep, 5
ControlSend, Scintilla1, {Space} , ahk_class Notepad++ ; Send Space character to Notepad++
Sleep, 5
MouseMove, xx, yy, 0 ; Move mouse cursor to original position
return

I can't explain it better.
I'll check your code as well, thanks.

P.S.
Is it possible to split an Array if the list is too big?
Example:

Instead of:

Code: Select all

cellArray := ["B4", "E4", "H4", "H9", "E9", "B9", "B15", "E15", "H15", "H20", "E20", "B20", "B26", "E26", "H26", "H31", "E31", "B31", "K4", "K9", "K14", "K19", "K25", "K30", "P4", "U4", "U9", "U14", "U19", "U30"]

Something like:

Code: Select all

cellArray := ["B4", "E4", "H4", "H9", "E9", "B9", "B15", "E15", "H15", "H20", "E20", "B20", "B26", "E26", "H26", 
                       "H31", "E31", "B31", "K4", "K9", "K14", "K19", "K25", "K30", "P4", "U4", "U9", "U14", "U19", "U30"]

Generally, I have no problem with a big array list.
However, the code gets "fat" just because of an array list.
The rest of the code takes like just half of the screen.

Regards

[jeeswg]

Commonly with AutoHotkey you can split lines up by putting a comma on a new line, e.g.

Code: Select all

oArray := ["A1", "B2"
, "C3", "D4"]

[Scr1pter]

Ok, that worked, thanks.

Do you know if I can write this easier?

Code: Select all

ControlSend, Scintilla1, ", ahk_class Notepad++ ; Send " character to Notepad++
Sleep, 5
ControlSend, Scintilla1, ^v, ahk_class Notepad++ ; Send copied value to Notepad++
Sleep, 5
ControlSend, Scintilla1, ", ahk_class Notepad++ ; Send " character to Notepad++
Sleep, 5
ControlSend, Scintilla1, `, , ahk_class Notepad++ ; Send , to Notepad++
Sleep, 5
ControlSend, Scintilla1, {Space} , ahk_class Notepad++ ; Send Space character to Notepad++
Sleep, 5

I tried to put them all in 1 line, but nothing got sent then.

Something like this

Code: Select all

ControlSend, Scintilla1, ", ^v, ", `,, {Space}, ahk_class Notepad++ ; Send " character to Notepad++

An alternative solution would be:

Code: Select all

Clipboard = "%Clipboard%",

After this I can simply write:

Code: Select all

ControlSend, Scintilla1, ^v, ahk_class Notepad++ ; Send copied value to Notepad++
Sleep, 5
ControlSend, Scintilla1, {Space} , ahk_class Notepad++ ; Send Space character to Notepad++
Sleep, 5

Then K1 gets converted to "K1",.
It's strange, because I already tried out several variations, but none of them worked.

P.S.
How to add space here?

Code: Select all

Clipboard = "%Clipboard%",

This didn't work:

Code: Select all

Clipboard = "%Clipboard%",A_Space

Thanks again for your help!

[jeeswg]

Some examples:
Note: {Text} requires v1.1.27.00.

Code: Select all

Clipboard = "%Clipboard%",%A_Space%
Clipboard := Chr(34) Clipboard Chr(34) ", "
Clipboard := Chr(34) Clipboard Chr(34) "," A_Space

q:: ;send to Edit control (e.g. Notepad)
vText := "abc"
vText := Chr(34) vText Chr(34) ", "
ControlSend, Edit1, % "{Text}" vText, A
return

w:: ;append to clipboard
vText := "abc"
Clipboard .= Chr(34) vText Chr(34) ", "
return

[Scr1pter]

Clipboard = "%Clipboard%",%A_Space% -> doesn't work.
Clipboard := Chr(34) Clipboard Chr(34) ", " -> works!

Thanks again, jeeswg

[jeeswg]

If it doesn't work, turn AutoTrim off. However, I would recommend to use expression style.

Code: Select all

;legacy style
AutoTrim, Off
Clipboard = "%Clipboard%",%A_Space%

;expression style
Clipboard := Chr(34) Clipboard Chr(34) ", "