Pull cell value from excel

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Monoxide3009
Posts: 65
Joined: 09 Apr 2018, 15:53

Pull cell value from excel

Post by Monoxide3009 » 15 Oct 2021, 12:57

Hello All,

I am fairly low tier, but I have a minor issue with pulling from excel and am hoping someone can help out.

I am trying to pull a cell exactly as it is, not as it is visually represented. For example, the cell is 162.72, but the width of the cell is small and cuts it down to 163. How can I pull 162.72?

My standard copy/paste function is, but it rounds in this specific circumstance:

Code: Select all

Test1:= wrkbk1.Sheets(SheetName).Range("AV"ROW).Text
I tried .value, but this comes out as 162.720000, which I feel is getting me closer, but still not what I am looking for:

Code: Select all

Test2:= wrkbk1.Sheets(SheetName).Range("AU"ROW).Value
I could use .value, then round it, but I deal with a ton of pulled variables and would like to find a better solution; a single line of code opposed to pulling the variable then manipulating it.

This is also a program used by less tech savvy people, so I need everything to function smoothly without further user input. As of now, the solution is to enforce them to make sure the spreadsheet they are using is sized accordingly, but less points of user error the better.

User avatar
Chunjee
Posts: 1397
Joined: 18 Apr 2014, 19:05
Contact:

Re: Pull cell value from excel

Post by Chunjee » 15 Oct 2021, 18:18

https://biga-ahk.github.io/biga.ahk/#/?id=trimend or similar can trim the 0's from the end without an additional line

Code: Select all

A := new biga() ; requires https://www.npmjs.com/package/biga.ahk

msgbox, % A.trimEnd(162.720000, 0)
; => 162.72
therefore

Code: Select all

msgbox, % A.trimEnd(wrkbk1.Sheets(SheetName).Range("AU"ROW).Value, 0)
; => 162.72

I do not know why excel does this.

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

Re: Pull cell value from excel

Post by flyingDman » 16 Oct 2021, 09:49

IMHO the only thing you can do is to temporarily increase the width of the column or to temporally decrease the font size i.e.

Code: Select all

#\::
xl := ComObjActive("excel.application")
ftsz := xl.activecell.font.size
xl.activecell.font.size := 1
cval := xl.activecell.text
xl.activecell.font.size := ftsz 
msgbox % cval

Not pretty, but fast enough and it gets the job done.
14.3 & 1.3.7

User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Pull cell value from excel

Post by FanaticGuru » 16 Oct 2021, 19:32

Chunjee wrote:
15 Oct 2021, 18:18
https://biga-ahk.github.io/biga.ahk/#/?id=trimend or similar can trim the 0's from the end without an additional line

I am not sure a whole library is need for such a simple task.

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % xlApp.Range("A1").Value
MsgBox % RTrim(xlApp.Range("A1").Value " " , " 0")

RTrim basically does it, just need to force the value to be a string.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

User avatar
Chunjee
Posts: 1397
Joined: 18 Apr 2014, 19:05
Contact:

Re: Pull cell value from excel

Post by Chunjee » 16 Oct 2021, 21:22

FanaticGuru wrote:
16 Oct 2021, 19:32
RTrim basically does it, just need to force the value to be a string.
Seems to work just fine without chaning the variable type.

Code: Select all

msgbox, % RTrim(162.720000, 0)
; => 162.72

FG

User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Pull cell value from excel

Post by FanaticGuru » 16 Oct 2021, 23:11

Chunjee wrote:
16 Oct 2021, 21:22
FanaticGuru wrote:
16 Oct 2021, 19:32
RTrim basically does it, just need to force the value to be a string.
Seems to work just fine without chaning the variable type.

Code: Select all

msgbox, % RTrim(162.720000, 0)
; => 162.72

If you try running the actual code, you should notice a difference.

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % xlApp.Range("A1").Value  ; 1.500000
MsgBox % RTrim(xlApp.Range("A1").Value, "0") ; 1.500000
MsgBox % RTrim(xlApp.Range("A1").Value, 0) ; 1.500000
MsgBox % RTrim(xlApp.Range("A1").Value " ", " 0") ; 1.5

Quote from Help File for RTrim "Any string value or variable. Numbers are not supported."

There is a difference between a hard typed number which RTrim will assume is a string since RTrim does not support numbers and what xlApp.Range("A1").Value returns which is a number.

As seen here:

Code: Select all

MsgBox % RTrim(162.720000, 0) ; 162.72 because assumes it is a string 
MsgBox % RTrim(162.720000 + 0, 0) ; 162.720000 because math forces typing to number and RTrim does nothing to numbers

These will give different results by the magic of AutoHotkey converting types on the fly depending on the circumstances.

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % RTrim(xlApp.Range("A1").Value, "0") ; 1.500000
Value := xlApp.Range("A1").Value
MsgBox % RTrim(Value, "0") ; 1.5

You can also condense the variable method like this: MsgBox % RTrim(Value := xlApp.Range("A1").Value, "0") ; 1.5

Now why all the various Trim commands don't just convert everything to a string automatically internally is a mystery. I guess just keeping the source code supper lean as it is rare with AutoHotkey's auto typing for a number to actually get passed to Trim.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

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

Re: Pull cell value from excel

Post by flyingDman » 17 Oct 2021, 00:30

In my world of financial ratios, 1.1 is not the same as 1.10 as the former might indicate that it had could have been 1.06 or 1.14. This might be an unacceptable range. If the ratio is shown as 1.10, it typically indicates a higher level of precision. Trimming zeros or rounding (which the OP does not want to do), affects this "precision". Trimming the 0s means that 1.10 will be reported as 1.1. This could be significant. If you want to report the cell content exactly as it is formatted - before being "rounded" by the fact that the column is too narrow - , this method is flawed.
14.3 & 1.3.7

User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Pull cell value from excel

Post by FanaticGuru » 18 Oct 2021, 15:48

flyingDman wrote:
17 Oct 2021, 00:30
In my world of financial ratios, 1.1 is not the same as 1.10 as the former might indicate that it had could have been 1.06 or 1.14. This might be an unacceptable range. If the ratio is shown as 1.10, it typically indicates a higher level of precision. Trimming zeros or rounding (which the OP does not want to do), affects this "precision". Trimming the 0s means that 1.10 will be reported as 1.1. This could be significant. If you want to report the cell content exactly as it is formatted - before being "rounded" by the fact that the column is too narrow - , this method is flawed.

I agree, 1.1 and 1.10 are not the same to me either. Basically it boils down to precision and significant digits.

But in this case with Excel, the only way I know to get the column width to cause rounding is if the cell is formatted "General" and "General" truncates trailing zeros. So removing the trailing zeros from the COM Value of a "General" cell is not a problem as the cell should have no trailing zeros.

If the cell is formatted "Number" the Excel user can set the precision and have trailing zeros but then if the column is too narrow to display completely, it will display "#####". It will not automatically round and display.

As I am creating both the Excel files and the AHK scripts I work with, I never really have a problem with formatting but if I was to have to work with some poorly formatted Excel files, the Font 1 trick might be useful. But what if the column is still too narrow at Font 1? :P

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

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

Re: Pull cell value from excel

Post by flyingDman » 18 Oct 2021, 15:57

FanaticGuru wrote:
18 Oct 2021, 15:48
But what if the column is still too narrow at Font 1? :P
Nothing is perfect.... :roll:
14.3 & 1.3.7

Post Reply

Return to “Ask for Help (v1)”