Saving contents of an excel cell Topic is solved

[JoshS611]

I've been developing a script to save a range of values (not all numerical) and for a long time have been copying the contents of the cell, using (stringtrimright, clipboard, clipboard, 2) and (Variable=%clipboard%) to save it as a variable and then moving to the next. I have found however that this is limiting my speed in segments such as this:

Code: Select all

Send, {right}{ctrl down}c{ctrl up}
StringTrimRight, clipboard, clipboard, 2
JProdPro=%clipboard%{space}
If JProdPro={space}
JProdPro=
Send, {right}{ctrl down}c{ctrl up}
StringTrimRight, clipboard, clipboard, 2
JMat=%clipboard%{space}
If JMat={space}
JMat=
Send, {right}{ctrl down}c{ctrl up}
StringTrimRight, clipboard, clipboard, 2
JComm=%clipboard%{space}
If JComm={space}
JComm=
Send, {right 4}{ctrl down}c{ctrl up}
StringTrimRight, clipboard, clipboard, 2
JDesc=%clipboard%,{space}

Essentially it often results in clipboard errors on excel due to multiple cells being copied in quick succession. I have been looking into using COM to get around this as not only will it save time due to skipping some send inputs, it will also prevent the clipboard bottleneck from slowing the script in general. So far I have:

Code: Select all

xl := ComObjActive("Excel.Application")
data := xl.ActiveCell.Value

This works great on cells only containing numbers, allowing me to instantly save the cell contents as a variable, however cells that contain any letters will have their variable saved as 0.

Is there a workaround to either of these options to save the contents of an excel cell (or perhaps another way around it that I haven't yet encountered)?

[Blackholyman]

well when you use COM you sometimes get stuff that the user does not normally see or even know about...

when grabing data from a cell in excel you have multiple options i'll name 3

.Value gives you the same as .Value2 except if the cell was formatted as currency or date then it gives you a something like a VBA currency (which may truncate decimal places) or VBA date or even Null.

.Text gives you a string, representing what is displayed on the screen for the cell. Using .Text is not allways a good idea because you could get ####

.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean) so with .value2 you get the real, underlying value of the cell. In case it's a date, it's a date serial: 41934.
So you can retrieve and store the value2 of a cell but note that the value will have some sort of innate type that you may have to act upon to get the right thing stored in your variable.

so must likely the format of your cell is whats giving you issues here... try with .text and see if that helps

[JoshS611]

well when you use COM you sometimes get stuff that the user does not normally see or even know about...

when grabing data from a cell in excel you have multiple options i'll name 3

.Value gives you the same as .Value2 except if the cell was formatted as currency or date then it gives you a something like a VBA currency (which may truncate decimal places) or VBA date or even Null.

.Text gives you a string, representing what is displayed on the screen for the cell. Using .Text is not allways a good idea because you could get ####

.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean) so with .value2 you get the real, underlying value of the cell. In case it's a date, it's a date serial: 41934.
So you can retrieve and store the value2 of a cell but note that the value will have some sort of innate type that you may have to act upon to get the right thing stored in your variable.

so must likely the format of your cell is whats giving you issues here... try with .text and see if that helps

Wow thanks for such a quick response, I'll try and get the text function going if it shows how it is shown on screen. I don't think things like #### being sent should be an issue as the spreadsheet information is taken from will have been checked beforehand. It is essentially a costing sheet and the info I am copying is line by line details of every item which someone will have physically entered at some point.

Will come back if I have any problems getting it running, thanks again!