How to copy a cells value to clipboard? Topic is solved
How to copy a cells value to clipboard?
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
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
- Attachments
-
- image.png (6.01 KiB) Viewed 1829 times
Re: How to copy a cells value to clipboard?
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%
Re: How to copy a cells value to clipboard?
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.
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.
Re: How to copy a cells value to clipboard? Topic is solved
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
}
- flyingDman
- Posts: 2791
- Joined: 29 Sep 2013, 19:01
Re: How to copy a cells value to clipboard?
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
Re: How to copy a cells value to clipboard?
mikeyww wrote: ↑27 Jan 2022, 07:09This 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
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.
Re: How to copy a cells value to clipboard?
This didn't work for me based on the values in clipboad.flyingDman wrote: ↑28 Jan 2022, 22:50If 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 }
Re: How to copy a cells value to clipboard?
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.
Re: How to copy a cells value to clipboard?
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
How to get rid of this?
Re: How to copy a cells value to clipboard?
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
Explained: #Warn
Re: How to copy a cells value to clipboard?
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
}
Re: How to copy a cells value to clipboard?
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
}
- flyingDman
- Posts: 2791
- Joined: 29 Sep 2013, 19:01
Re: How to copy a cells value to clipboard?
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
Re: How to copy a cells value to clipboard?
mikeyww wrote: ↑07 Feb 2022, 11:57Idea:
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
Where can I learn more about these methods you use here so I know what does what. I want to learn some
Thank you, I really appreciate your time!
Re: How to copy a cells value to clipboard?
It was too hard for me :SflyingDman wrote: ↑07 Feb 2022, 12:03My 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.
Re: How to copy a cells value to clipboard?
If you click on the AHK functions and commands in the posted script, you can learn more about them.