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.
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.
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 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.
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.