AppleScript Excel - Copy value of cells into variables

Question

Level 1

5 points

AppleScript Excel - Copy value of cells into variables

I am super new to AppleScript, so please excuse me if I am not good at explaining what I am trying to do. I have an Excel file that has 12 columns and X rows, I need to have B2/C2/D2/etc copied into variables but have the column be a variable instead of a static number so I can write a loop to get all the rows, etc. Here is what I have now, and I know this is far from being correct.

copy value of cell "B2" to varRow

I have tried something crazy like this:

copy value of column colSection of row intRow to varSection

But that just throws up an error saying the object i am trying to access does not exist. I set the ColSection to "B" and intRow to 2, so I thought it would work. Any ideas or advice I am open! Thanks!

Mac Pro, Mac OS X (10.6.6)

Posted on Jan 19, 2011 9:41 AM

Reply

Answer 1

Best reply

Jim Reece

Level 1

10 points

Jan 19, 2011 4:47 PM in response to JeffStutsman

The following should give you the idea:

tell application "Microsoft Excel"
activate
make new workbook
set myCol to 3 -- the starting column
set myBgnRow to 2 -- the starting row

set myString to "Hello World" -- characters to be inserted into sheet as sample values
set myChar to 1 -- Points to first character ("H")

set myEndRow to myBgnRow + (length of myString) - 1 -- the ending row

-- Now put some values into the cells

repeat with myRow from myBgnRow to myEndRow
select (cell myCol of row myRow of active sheet)
tell cell myCol of row myRow
set value to character myChar of myString
end tell
set myChar to myChar + 1
end repeat

-- now get the values back out
-- note that this is using same myBgnRow and myEndRow as above

repeat with myRow from myBgnRow to myEndRow
select (cell myCol of row myRow of active sheet)
tell cell myCol of row myRow
set myVal to value
end tell
display dialog (myCol as text) & "," & (myRow as text) & " " & myVal
end repeat

end tell

Reply

Answer 2

JeffStutsman Author

Level 1

5 points

Jan 20, 2011 7:45 AM in response to Jim Reece

I appreciate your reply, but I am still a little lost and need some help. I looked at my original post and did not explain myself very well at all and for that I apologize. My excel spreadsheet has 12 columns, and right now has 15 rows. All 15 rows have data for all 12 columns, what I need to do is take all the data and put them into a list/array where I can access the data for each row. The rows on my excel file are 15 now but could be 2 or 50 or 125 so I need to have this AppleScript be able to read all the active rows. So I see in your script where you can pull out the data, is there anyway to put all the data from my excel file into an array/list? I am trying to find info online and just not having any luck. Thanks and sorry to bother you dumb questions.

Reply

Answer 3

Jan 20, 2011 10:02 AM in response to JeffStutsman

Hi

JeffStutsman wrote:
what I need to do is take all the data and put them into a list/array

tell application "Microsoft Excel"
 tell active sheet to set allData to value of used range
end tell

to get number of rows in the varable allData

set numberOF_rows to count lists of allData

JeffStutsman wrote:
where I can access the data for each row.

to get row 3 in the varable allData

set row3 to list 3 of allData

Reply

Answer 4

Jan 20, 2011 11:30 AM in response to JeffStutsman

This should move all the data into a "list of lists" and then retrieve the data from any specific cell

tell application "Microsoft Excel"

-- put the complete set of data into a list of lists (i.e., 2 dimensions -> columns of rows)
tell active sheet to set myData to value of used range

-- now access a specific cell's data
set myRow to 7
set myCol to 3
set myVal to item {myCol} of item {myRow} of myData

end tell

Reply

Answer 5

JeffStutsman Author

Level 1

5 points

Jan 20, 2011 11:58 AM in response to Jim Reece

This is exactly what I needed! There will always be another question, but thank you so much for helping me with this one!

Reply