Inserting text in an Excel Document

Hello.

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

Any help would be greatly appreciated.

thanks,

  • Bruce

You’ve got it!

That script uses Get Offset and its row offset argument to refer to the cell below lastFilledCell.
Get Offset also has a column offset argument.

This works great!

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”)

Thanks for the responses.

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.

Thanks again,

  • Bruce

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.

B65536 refers to Row 65536 of Column B

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.

Thanks again,

  • Bruce