Help with incrementing and excel cell

Scripting Forums AppleScript | Mac OS X
#1

I have been trying to find a way to increment by +1 the last available cell in an excel spreadsheet without any success.

I can locate the last empty cell in my range but cannot find the syntax to get the last cell value and increment it by +1?

Can anyone point out a syntax to do so?

Here is what I have for finding the last empty cell and trying to increment it!


tell application "Microsoft Excel"
	tell active sheet
		set usedRange to used range
		set firstUnusedRow to row ((count rows of usedRange) + 1)
		set nextUnusedRow to (value of usedRange) + 1
	end tell
end tell

Thanks

#2

Hi,

the reference to the last used row is


set lastUsedRow to row (count rows of usedRange)

the reference to the first unused row is


set firstUnusedRow to row ((count rows of usedRange) + 1)

to get the value of column A of the last used row


set lastUsedRowValue to value of column 1 of lastUsedRow
#3

Hello Stefan,

Thanks for your help here is my working script.


tell application "Microsoft Excel"
	tell active sheet
		set usedRange to used range
		set lastUsedRow to row (count rows of usedRange)
		set lastUsedRowValue to (value of column 1 of lastUsedRow as integer) + 1
	end tell
end tell

How can I add leading 000 in front of the incremented value? Now if the last cell value is “5” then the script gives me “6” but actually I would like it to return “0006”?

Thanks,

#4

If you want to keep the numeric value of the cell I recommend to change the format of the cell respectively

#5

Hey Stefan I found a solution that is working what do you think about it?


tell application "Microsoft Excel"
	tell active sheet
		set usedRange to used range
		set lastUsedRow to row (count rows of usedRange)
		set lastUsedRowValue to (value of column 1 of lastUsedRow as integer) + 1
		set n to lastUsedRowValue
		set padded_number to text -4 thru -1 of ("0000" & n)
	end tell
end tell