I’m trying to insert text from variables in an Excel document and I want to have a series of variables show up in consecutive rows. I figured out (from searching the forums) how to find the last filled cell and insert text below it, but what I’m wondering now is how I would insert text to the right of the last filled cell. Here’s an example of what I have with two variables. One is inserted below the other:
tell application "Microsoft Excel"
activate
tell worksheet 2 of document 1
set lastfilledcell to get end (range "B65536") direction toward the top
set firstBlankCell to get offset lastfilledcell row offset 1
set value of firstBlankCell to annot_fullname
set lastfilledcell to get end (range "B65536") direction toward the top
set firstBlankCell to get offset lastfilledcell row offset 1
set value of firstBlankCell to annot_artist
end tell
end tell
tell application "Microsoft Excel"
activate
tell worksheet 2 of document 1
set lastfilledcell to get end (range "B65536") direction toward the top
set firstBlankCell to get offset lastfilledcell row offset 1
set value of firstBlankCell to annot_fullname
set lastfilledcell to get end (range "C65536") direction toward the top
set firstBlankCell to get offset lastfilledcell row offset 1
set value of firstBlankCell to annot_artist
end tell
end tell
Simply change the reference of the cell: (range “B65536”) to (range “C65536”)
This seems to be working. Another question, though: How does this range command work? I don’t understand what I’m saying by specifying range B65536 or range C65536.
Okay, now I realize the obvious, which is that the B at the beginning specifies the row. I still don’t know what the number after it means, but now that I sort of understand “get end” and column / row offset, this works for me:
set lastfilledcell to get end (range "title") direction toward the top
set title_cell to (get offset lastfilledcell row offset 1)
set value of title_cell to annot_fullname
set artist_cell to (get offset title_cell column offset 1)
set value of artist_cell to annot_artist
set genre_cell to (get offset artist_cell column offset 1)
set value of genre_cell to annot_genre
I’ll repeat this for the list of 19 attributes, so they fill in to the right. Next I have to learn some more about folder actions so I can make this happen for each file in a folder.
The reason for 65536 is that, until Excel 2007/2008, that was the maximum number of rows on an Excel spreadsheet.
To get the last filled cell in column B, even if there are more than 65536 rows, one would use one of the two last styles.
tell application "Microsoft Excel"
set myCell to range "B65536" -- old style
set myCell to range ("B" & ((count of rows) as text)) -- new style (A1 aproach)
set myCell to cells of row (count of rows) of column 2 -- new style (R1C1 approach)
set myLastCell to get end myCell direction toward the top
return get address myLastCell
end tell
One issue with the Get End approach is that a cell that holding a formula that evaluates to “” is counted as filled.
The above script will return the last cell that is filled, with either a value or a formula. myLastCell might appear blank to the user.
To get the last cell in column B that is not blank (i.e. shows nothing when the user looks at it), use the script below. There may be cells containing formulas below LastCell2, but they will all evaluate to “”.
tell application "Microsoft Excel"
set LastCell2 to find range "B:B" what "?*" after range "B1" look in values search direction search previous
get address LastCell2
end tell
Thanks for the clarification. I had to move on to something else shortly after I posted this, but now that I’m back to it, your answer made everything work well.