Friday, October 31, 2014

#1 2013-02-14 05:07:58 pm

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

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


Filed under: excel mac

Offline

 

#2 2013-02-19 07:41:42 am

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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,

Offline

 

#3 2013-02-19 08:01:55 am

StefanK
Member
From: St. Gallen, Switzerland
Registered: 2006-10-21
Posts: 10624
Website

Re: Scripting Excel Mac 2008

Hi,

you can find the first blank row with

Applescript:


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


regards

Stefan

Offline

 

#4 2013-02-19 08:03:55 am

SuperMacGuy
Member
From: Amish Country, Lancaster, PA
Registered: 2004-06-23
Posts: 416
Website

Re: Scripting Excel Mac 2008

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.


Scripts for InDesign: http://chris.paveglio.com
ASOC & Cocoa, specializing in InDesign, Photoshop, Ai, FileMaker

Offline

 

#5 2013-02-19 11:34:28 am

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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.

Offline

 

#6 2013-02-20 11:08:47 am

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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

Applescript:

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,

Last edited by sharingsunshine (2013-02-20 11:09:55 am)

Offline

 

#7 2013-02-20 03:53:19 pm

Shane Stanley
Member
From: Australia
Registered: 2002-12-07
Posts: 3729

Re: Scripting Excel Mac 2008

Applescript:

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

Or more usefully:

Applescript:

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


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/

Offline

 

#8 2013-02-20 04:02:19 pm

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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

Offline

 

#9 2013-02-20 04:10:36 pm

Shane Stanley
Member
From: Australia
Registered: 2002-12-07
Posts: 3729

Re: Scripting Excel Mac 2008

sharingsunshine wrote:

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.


Shane Stanley <sstanley@myriad-com.com.au>
www.macosxautomation.com/applescript/apps/

Offline

 

#10 2013-02-20 04:16:22 pm

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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

Offline

 

#11 2013-02-21 11:13:01 am

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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.

Applescript:

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

Applescript:

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

Offline

 

#12 2013-02-21 04:28:21 pm

sharingsunshine
Member
Registered: 2013-01-04
Posts: 15

Re: Scripting Excel Mac 2008

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.

Applescript:

   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

Applescript:

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

Last edited by sharingsunshine (2013-02-21 04:29:13 pm)

Offline

 

Board footer

Powered by FluxBB

[ Generated in 0.045 seconds, 10 queries executed ]

RSS (new topics) RSS (active topics)