PDA

View Full Version : Applescript Question in Excel 2008




merissa1408
Feb 1, 2009, 09:14 PM
I've hit a spot in my coding that I just can't figure out!

The goal of my code: Insert records into an excel spreadsheet. You know, name, date, product number, quantity, cost. I want them to go across the row from A-F... however, not necessarily in row 1. I'm trying to allow myself to input the row I want the data to go to (using a dialogue box), and then it all goes there. But in my code, that doesn't work. Also, what I want is that when I input new data, the current data shifts down a row (so nothing becomes overwritten).
Please read my code, be warned it's messy, and try to help me as best you can!!! Greatly appreciated.


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 "Pickup 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 "Product #" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog2 to result
set product to text returned of dialog2
if button returned of dialog2 is "NEXT" then display dialog "Customer" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog3 to result
set customer to text returned of dialog3
if button returned of dialog3 is "NEXT" then display dialog "Quantity" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog4 to result
set qty to text returned of dialog4
if button returned of dialog4 is "NEXT" then display dialog "Sizes" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog5 to result
set sizes to text returned of dialog5
if button returned of dialog5 is "NEXT" then display dialog "Amount Due" default answer "" buttons {"Finished"} default button 1 with icon caution
set dialog6 to result
set due to text returned of dialog6

set value of cell "A14" to pickup
set value of cell "B14" to product


end tell


any ideas??? =) please and thank you!



merissa1408
Feb 26, 2009, 07:58 AM
im shocked that none of you can help!

xyzeugene
Feb 26, 2009, 08:38 AM
Why don't you use VBA which is native scripting to excel? Makes life alot easier.

I've hit a spot in my coding that I just can't figure out!

The goal of my code: Insert records into an excel spreadsheet. You know, name, date, product number, quantity, cost. I want them to go across the row from A-F... however, not necessarily in row 1. I'm trying to allow myself to input the row I want the data to go to (using a dialogue box), and then it all goes there. But in my code, that doesn't work. Also, what I want is that when I input new data, the current data shifts down a row (so nothing becomes overwritten).
Please read my code, be warned it's messy, and try to help me as best you can!!! Greatly appreciated.


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 "Pickup 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 "Product #" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog2 to result
set product to text returned of dialog2
if button returned of dialog2 is "NEXT" then display dialog "Customer" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog3 to result
set customer to text returned of dialog3
if button returned of dialog3 is "NEXT" then display dialog "Quantity" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog4 to result
set qty to text returned of dialog4
if button returned of dialog4 is "NEXT" then display dialog "Sizes" default answer "" buttons {"Cancel", "NEXT"} default button 2 with icon caution
set dialog5 to result
set sizes to text returned of dialog5
if button returned of dialog5 is "NEXT" then display dialog "Amount Due" default answer "" buttons {"Finished"} default button 1 with icon caution
set dialog6 to result
set due to text returned of dialog6

set value of cell "A14" to pickup
set value of cell "B14" to product


end tell


any ideas??? =) please and thank you!

merissa1408
Feb 26, 2009, 11:20 AM
Why don't you use VBA which is native scripting to excel? Makes life alot easier.

i had no idea i could use VBA on my mac!! lol do you know where i could find it?

lee1210
Feb 26, 2009, 11:29 AM
i had no idea i could use VBA on my mac!! lol do you know where i could find it?

For better or worse (most people say worse), VBA is no longer supported in Office Mac, as of the 2008 release.

-Lee

merissa1408
Feb 26, 2009, 02:05 PM
For better or worse (most people say worse), VBA is no longer supported in Office Mac, as of the 2008 release.

-Lee

oh yeah haha. i did actually know that, and that is why i started using applescript to begin with. i completely forgot lol. thanks Lee

so back to my original question... anyone know how to fix my coding?? lol

SwiftLives
Feb 26, 2009, 02:19 PM
These guys (http://macscripter.net/) might be more helpful. (Not to discount the good folk here at MR).

merissa1408
Feb 26, 2009, 02:51 PM
These guys (http://macscripter.net/) might be more helpful. (Not to discount the good folk here at MR).

thanks!

Zyniker
Feb 27, 2009, 01:49 AM
For better or worse (most people say worse), VBA is no longer supported in Office Mac, as of the 2008 release.

-Lee

VBA support will be restored to the Mac in the next release of Office Mac (2010 or 2011). :)

merissa1408
Mar 3, 2009, 12:18 PM
VBA support will be restored to the Mac in the next release of Office Mac (2010 or 2011). :)

good to know, thanks!

sfshah
Jul 1, 2010, 11:23 AM
I tried the script as per #3, it does not insert the row and inserts data in row 14, up to column C only.