Transferring the values from a known range of cells (containing formulas) in Excel using Applescript

Question

Level 2

371 points

Transferring the values from a known range of cells (containing formulas) in Excel using Applescript

I have my AppleScript iterating thru email messages and pasting data (which arrives via email from a web form) into following cells: range "A1:A50"

My Excel parsing of data occurs in cell formulas on Row 52, cells in range "A52:AZ52"

I want to copy the cells in row 52 and paste the Values (not the formulas) in successive rows underneath the formula cells, starting with row 54: range "A54:AZ54"

In pseudo code the code snip is:

tell worksheet "sheet1" of workbook "Example" application "Microsoft Excel"

repeat i from 1 thru to (count emails)

paste some of the contents of (email item i) into sheet range "A1:A50"

# the above forms about twenty lines of code to get it
into Excel's cell object list or lists

copy or get row 52 # or range "A52:AZ52" if that's easier/faster/better

paste or set the values of row 52 into row (53+i)

end repeat

end tell

Not sure whether to use the clipboard with "paste speical…[range] with []" or copy and set value statements. I only know how to reference existing ranges, not the cell contents of the range. I really need to know how to get contents of cells into a AppleScript object (list) that I can transfer. The 2004 Excel AS documentation is not helping me, please throw me a life line someone. :-)

MacBook Pro, Mac OS X (10.7.5), Wacom tablet, Dell 27" U2713HM,

Posted on Mar 13, 2014 11:49 PM

Reply

Answer 1

Best reply

Jacques Rioux

Level 4

3,498 points

Mar 14, 2014 7:59 AM in response to wideEyedPupil

Hi

Use value , like this

tell application "Microsoft Excel"
          set tValues to value of range "A52:AZ52"
          set value of range "A54:AZ54" to tValues
end tell

Reply

Answer 2

wideEyedPupil Author

Level 2

371 points

Mar 14, 2014 5:46 PM in response to Jacques Rioux

One other question if I may hijack my thread, if I wanted to set the column of the range programatically how would I do that?

I know how to programatically set the row eg.

set x to 54

set r to range "A" & x & ":AZ" & x

set tVlaue to value of r

But I need something like Column (x) to make one of {"A","B","C",…}

but column(2) gives me $B:B$

and: "column(2) as text" (so I can halve it) beachballs Script Debugger and Excel and I have to stop the script and restart Excel!

Reply