How to copy a cells value to clipboard? Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

How to copy a cells value to clipboard?

Post by Krd » 27 Jan 2022, 06:08

Hello,

I have a system which where I copy the whole line it pastes columns A to P and Raw 1 and 2.
In Raw 2 Column J is the value that I want to extract to Clipboard. How to do that?

Just to say it. This is not happening in Excel. My system has columns and raws which where I copy from and paste into excel it looks and behaves Like excel when I paste its values in Excel.

So how to think like Excel and extract value J which is alleready in Clipboard from all other Columns(16 Columns) and Raws (2 raws).

See the example below which is in Clipboard as a whole. How to extract just the value 1 000,00 (It could be 0,00 to 1 000 000,00 Currency).

I ! A S Det F R ST SS HH SE SEE KK SKB SA KM
654321 27.01.2022 123456 W L 12345678910 27.01.2022 N M ABC 1 000 A a b 1234 cc Some A name
image.png
image.png (5.97 KiB) Viewed 1829 times
Attachments
image.png
image.png (6.01 KiB) Viewed 1829 times

User avatar
mikeyww
Posts: 26596
Joined: 09 Sep 2014, 18:38

Re: How to copy a cells value to clipboard?

Post by mikeyww » 27 Jan 2022, 06:27

Code: Select all

cell = J2
XL := ComObjActive("Excel.Application")
Clipboard := "", Clipboard := Round(XL.Range(cell).Value, 2)
ClipWait, 0
If ErrorLevel
 MsgBox, 48, Error, An error occurred while waiting for the clipboard.
MsgBox, 64, J2, %Clipboard%
image220127-0623-001.png
Output
image220127-0623-001.png (5.69 KiB) Viewed 1809 times

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 27 Jan 2022, 06:29

Thanks for the replay.

The operation is not available.

As stated this is happening in Clipboard and Excel is not connected at all.

It just behaves like Excel when it comes to values when I copy and paste them in Excel or else where.

I am more after some Regex or substr methods as the values in Clipboard are not in Excel.

User avatar
mikeyww
Posts: 26596
Joined: 09 Sep 2014, 18:38

Re: How to copy a cells value to clipboard?  Topic is solved

Post by mikeyww » 27 Jan 2022, 07:09

This may work for the first 26 columns!

Code: Select all

Clipboard =
(
I	!	A	S	Det	F	R	ST	SS	HH	SE	SEE	KK	SKB	SA	KM
654321	27.01.2022	123456	W L	12345678910	27.01.2022	N	M	ABC	1000	A	a	b	1234	cc	Some	A	name
)
Sleep, 100
; ------------------------------------------

cell = J2
MsgBox, 64, %cell%, % cell(Clipboard, cell, 2)

cell(str, oneCellRange, digits := 0) {
 col  := Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64
 row  := RegExReplace(oneCellRange, "\D+")
 line := StrSplit(str, "`n"), cell := StrSplit(line[row], "`t")[col]
 If cell is number
      Return Round(cell, digits)
 Else Return cell
}

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 27 Jan 2022, 08:53

mikeyww wrote:
27 Jan 2022, 07:09

WOW, the magic of autohotkey! This is so big for me to get it to work. You are an angel.

Thank you so much :D

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to copy a cells value to clipboard?

Post by flyingDman » 28 Jan 2022, 22:50

If your column count > 26, use this:

Code: Select all

cell := "JJ2"
msgbox % col := s2n(substr(cell,1,(cell~="\d") - 1))
msgbox % row := substr(cell,cell~="\d")

S2N(col){
	for x,y in z:=strsplit(Format("{:U}", col))
		tmp += (asc(y)-64)*26**(z.length()-x)
	return +tmp
	}
14.3 & 1.3.7

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 07 Feb 2022, 04:56

mikeyww wrote:
27 Jan 2022, 07:09
This may work for the first 26 columns!

Code: Select all

Clipboard =
(
I	!	A	S	Det	F	R	ST	SS	HH	SE	SEE	KK	SKB	SA	KM
654321	27.01.2022	123456	W L	12345678910	27.01.2022	N	M	ABC	1000	A	a	b	1234	cc	Some	A	name
)
Sleep, 100
; ------------------------------------------

cell = J2
MsgBox, 64, %cell%, % cell(Clipboard, cell, 2)

cell(str, oneCellRange, digits := 0) {
 col  := Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64
 row  := RegExReplace(oneCellRange, "\D+")
 line := StrSplit(str, "`n"), cell := StrSplit(line[row], "`t")[col]
 If cell is number
      Return Round(cell, digits)
 Else Return cell
}

Hello,

I can't get this to work with another use. Now I want this to work for Cell D 1 in Clipboard. How to adjust your code to this:

sum 10 000 2 139 000 14 0
image.png
image.png (1.49 KiB) Viewed 1641 times

I now want to copy only the 10 000 from clipboard which is like D1, there may or maybe not be other values further from D1 but it can happen and we just ignore those.
Last edited by Krd on 07 Feb 2022, 04:58, edited 1 time in total.

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 07 Feb 2022, 04:57

flyingDman wrote:
28 Jan 2022, 22:50
If your column count > 26, use this:

Code: Select all

cell := "JJ2"
msgbox % col := s2n(substr(cell,1,(cell~="\d") - 1))
msgbox % row := substr(cell,cell~="\d")

S2N(col){
	for x,y in z:=strsplit(Format("{:U}", col))
		tmp += (asc(y)-64)*26**(z.length()-x)
	return +tmp
	}
This didn't work for me based on the values in clipboad.

User avatar
mikeyww
Posts: 26596
Joined: 09 Sep 2014, 18:38

Re: How to copy a cells value to clipboard?

Post by mikeyww » 07 Feb 2022, 07:23

My script does work for cell D1. It is using Tab as the delimiter, so your text string would need to do that. If you have no delimiters, then there might be no reliable way to distinguish the cell boundaries.

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 07 Feb 2022, 07:59

mikeyww wrote:
07 Feb 2022, 07:23
My script does work for cell D1. It is using Tab as the delimiter, so your text string would need to do that. If you have no delimiters, then there might be no reliable way to distinguish the cell boundaries.
After some try and fails I added it to my new script:

Code: Select all

F12::     ;Find Sum and add it to Clipbaord
Send, {Ctrl Down}f{Ctrl Up}
Sleep, 300
Send, {Backspace}
Sleep, 100
Send, sum
Sleep, 100
Send, {Ctrl Down}a{Ctrl Up}
Sleep, 200
Send, {Backspace}
Sleep, 100
Send, sum
Sleep, 100
Send, {Escape}
Sleep, 100
Send, {Shift Down}{Ctrl Down}{Right 4}{Ctrl Up}{Shift Up}
Sleep, 700
Send, {Ctrl Down}c{Ctrl Up}
Sleep, 1000
cell = D1
Sum :=  cell(Clipboard, cell, 1)
cell(str, oneCellRange, digits := 0) {
 col  := Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64
 row  := RegExReplace(oneCellRange, "\D+")
 line := StrSplit(str, "`n"), cell := StrSplit(line[row], "`t")[col]
 If cell is number
      Return Round(cell, digits)
 Else Return cell
}
Sleep, 1000
Clipboard := Sum
ClipWait, 1
return
It seems it is working but I get this every time i reload the script, I have #warning on.
image.png
image.png (18.07 KiB) Viewed 1603 times

How to get rid of this?

User avatar
mikeyww
Posts: 26596
Joined: 09 Sep 2014, 18:38

Re: How to copy a cells value to clipboard?

Post by mikeyww » 07 Feb 2022, 08:08

You are getting the warning because "cell" is used inside and outside the function. You can alter the warning directive, remove it, or change your global variable name (i.e., "cell" outside the function).

Explained: #Warn

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 07 Feb 2022, 11:55

mikeyww wrote:
07 Feb 2022, 08:08
You are getting the warning because "cell" is used inside and outside the function. You can alter the warning directive, remove it, or change your global variable name (i.e., "cell" outside the function).

Explained: #Warn
Lol, really can't know where to put, I did try but no luck. I want the warn on, what to change in order to change global variable name? It is too much for a noob :S

Code: Select all

cell = D1
Sum :=  cell(Clipboard, cell, 1)
cell(str, oneCellRange, digits := 0) {
 col  := Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64
 row  := RegExReplace(oneCellRange, "\D+")
 line := StrSplit(str, "`n"), cell := StrSplit(line[row], "`t")[col]
 If cell is number
      Return Round(cell, digits)
 Else Return cell
}

User avatar
mikeyww
Posts: 26596
Joined: 09 Sep 2014, 18:38

Re: How to copy a cells value to clipboard?

Post by mikeyww » 07 Feb 2022, 11:57

Idea:

Code: Select all

Sum := cell(Clipboard, "D1", 1)
cell(str, oneCellRange, digits := 0) {
 col  := Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64
 row  := RegExReplace(oneCellRange, "\D+")
 line := StrSplit(str, "`n"), cellVal := StrSplit(line[row], "`t")[col]
 If cellVal is number
      Return Round(cellVal, digits)
 Else Return cellVal
}

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to copy a cells value to clipboard?

Post by flyingDman » 07 Feb 2022, 12:03

My script only allows you to transform cell addresses in the form "A1" to column number, row number and works with columns > 26. So AA1 is transformed to column 27 and row 1. You will have to use that instead of Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64 which only works with columns 1 - 26.
14.3 & 1.3.7

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 07 Feb 2022, 12:21

mikeyww wrote:
07 Feb 2022, 11:57
Idea:

Code: Select all

Sum := cell(Clipboard, "D1", 1)
cell(str, oneCellRange, digits := 0) {
 col  := Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64
 row  := RegExReplace(oneCellRange, "\D+")
 line := StrSplit(str, "`n"), cellVal := StrSplit(line[row], "`t")[col]
 If cellVal is number
      Return Round(cellVal, digits)
 Else Return cellVal
}

But a great idea that works, uufff spoon feeding me :crazy:

Where can I learn more about these methods you use here so I know what does what. I want to learn some :D


Thank you, I really appreciate your time!

Krd
Posts: 405
Joined: 10 Mar 2020, 02:46

Re: How to copy a cells value to clipboard?

Post by Krd » 07 Feb 2022, 12:25

flyingDman wrote:
07 Feb 2022, 12:03
My script only allows you to transform cell addresses in the form "A1" to column number, row number and works with columns > 26. So AA1 is transformed to column 27 and row 1. You will have to use that instead of Asc(Format("{:U}", RegExReplace(oneCellRange, "\d+"))) - 64 which only works with columns 1 - 26.
:headwall: It was too hard for me :S

User avatar
mikeyww
Posts: 26596
Joined: 09 Sep 2014, 18:38

Re: How to copy a cells value to clipboard?

Post by mikeyww » 07 Feb 2022, 12:35

If you click on the AHK functions and commands in the posted script, you can learn more about them.

Post Reply

Return to “Ask for Help (v1)”