Scripting Excel Mac 2008

I need to know how to script a form for my excel worksheet. I have looked at the data form option and I need more functionality than it provides.

In particular, there is already data in the worksheet and I need to add a row of data through a form and each time it adds the row it needs to keep track so that the next blank row is used. So in my limited knowledge it needs to keep track of what it just posted and go to the next blank row. There won’t be any skipping around so it will always be the next row.

The columns are as follows:

Date
Name
Memo
Amount
Balance - this is a calculated running total

I went through many of the tutorials and didn’t find how to address this process. If there is one please let me know I am wanting to learn how to do this rather than just get the answer.

Thanks,

Randal

Can anyone tell me how to script the data → forms option in Excel 2008? I can work out the extra functionality but I don’t see in the Excel dictionary a way to activate the data → forms option.

Thanks,

Hi,

you can find the first blank row with


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

assuming the sheet contains only the 5 data columns

It’s ugly but you could use GUI scripting to select the menu item. Is it enough that the window pops open?
I don’t know much about this functionality. When I have scripted Excel I usually added or deleted rows “manually” with scripting and inserted the data via script (in a loop or something to fill in cells). If you need to add columns or rows maybe consider doing it like that.

Thanks for the responses. I thought I could just call the data → forms option then use it from there. I will have to do some more studying on how to add by rows like you suggested.

Ok, I can get this script to add the rows but it is by specifying the row values in the script. I need to know how to make part of the cell reference dynamic. In the case of my script it would be the value “r.”

tell application "Microsoft Excel"
	activate
	display dialog "Input Row" default answer "" buttons {"OK"} default button 1
	set blank to result
	set r to text returned of blank
	
	display dialog "Date" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog1 to result
	set pickup to text returned of dialog1
	
	if button returned of dialog1 is "NEXT" then display dialog "Name" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog2 to result
	set monthly_name to text returned of dialog2
	
	if button returned of dialog2 is "NEXT" then display dialog "Memo" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog3 to result
	set memo to text returned of dialog3
	
	if button returned of dialog3 is "NEXT" then display dialog "Amount" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog4 to result
	set amount to text returned of dialog4
	
	set value of cell "A15" to pickup
	set value of cell "B15" to monthly_name
	set value of cell "C15" to memo
	set value of cell "D15" to amount
	
	
end tell

In this script “r” would take the place of the 15.

Thanks,

set value of cell ("A" & r) to pickup

Or more usefully:

set value of range ("A" & r & ":D" & r) to {pickup, monthly_name, memo, amount}

this looks great but I don’t know how to make this character & or what it does?

Hold down the shift key and press 7.

Mine didn’t look exactly like yours but I realized it was the Ampersand character. That works great on adding the row.

Thanks again for getting past the dynamic row problem. Now I am wanting to improve this to find the unused row automatically. I need this because I need an entry to be put in by Calendar automatically on a monthly basis. Since it won’t have any intervention it has to be able to find the first unused row on its own. Here is what I have and I am using Stefan’s suggestion but I am getting an error.

tell application "Microsoft Excel"
	
	tell active sheet
		set usedRange to used range
		set firstUnusedRow to row ((count row of usedRange) + 1)
	end tell
	
	
	display dialog "Date" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog1 to result
	set pickup to text returned of dialog1
	
	if button returned of dialog1 is "NEXT" then display dialog "Name" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog2 to result
	set monthly_name to text returned of dialog2
	
	if button returned of dialog2 is "NEXT" then display dialog "Memo" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog3 to result
	set memo to text returned of dialog3
	
	if button returned of dialog3 is "NEXT" then display dialog "Amount" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog4 to result
	set amount to text returned of dialog4
	
	set value of cell ("A" & firstUnusedRow) to pickup
	set value of cell ("B" & firstUnusedRow) to monthly_name
	set value of cell ("C" & firstUnusedRow) to memo
	set value of cell ("D" & firstUnusedRow) to amount
	
	
end tell

I am getting this error

error "Can't make «class crow» \"$38:$38\" of «class 1107» of application \"Microsoft Excel\" into type Unicode text." number -1700 from «class crow» "$38:$38" of «class 1107» to Unicode text

for some reason it is coming up with row 38 and in my sheet it should be 6.

Thanks

Randal

I changed the beginning of the code to this snippet (to find the first empty row) and now it is working without giving any errors.

	tell application "Microsoft Excel"
		repeat with x from 4 to (count rows of active sheet)
			if value of cell 1 of row x = "" then exit repeat
		end repeat
	end tell

I start at row 4 because I have Column headings in row 1 then I have 2 blank lines so the entries begin on row 4. I have the amount and balance field already formatted so they aren’t blank but the code above is only looking to see if cell 1 is blank so it works great.

In case, someone wants to see the all of the completed code here it is

tell application "Microsoft Excel"
	
	tell application "Microsoft Excel"
		repeat with x from 4 to (count rows of active sheet)
			if value of cell 1 of row x = "" then exit repeat
		end repeat
	end tell
	
	
	display dialog "Date" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog1 to result
	set pickup to text returned of dialog1
	
	if button returned of dialog1 is "NEXT" then display dialog "Name" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog2 to result
	set monthly_name to text returned of dialog2
	
	if button returned of dialog2 is "NEXT" then display dialog "Memo" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog3 to result
	set memo to text returned of dialog3
	
	if button returned of dialog3 is "NEXT" then display dialog "Amount" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
	set dialog4 to result
	set amount to text returned of dialog4
	
	
	set value of cell ("A" & x) to pickup
	set value of cell ("B" & x) to monthly_name
	set value of cell ("C" & x) to memo
	set value of cell ("D" & x) to amount
	
	
end tell

This is a great forum and you all have been very helpful.

Randal