AppleScript or app development help

[mattrstewart]

i have a task that I need help with developing...as i know nothing of this...not sure if it should be an applescript or an app.

does someone know where i can go to find a person who would be willing to help me make this?
it should be pretty darned simple for someone who knows about scripting

[macbri]

Why not write a few lines on what you're trying to do. You'll most likely get suggestions on how to do it, where to look for help, and maybe someone's already done something similar we can point you at.

[mattrstewart]

well....basically I have a excel document (.xls) thats used as a template for each product's schedule that our company makes.

the schedule has a bunch of different information, and the project name, and the dates each stage is ready on.

I want to make a script or an app or something....that will read the designated excel file and transfer certain information to iCal.

For example....
lets say i have a schedule "ProjectONE.xls"
"ProjectONE" would be the project name, which is also listed in one of the excel cells.
The schedule then has 15 stages (StageONE, StageTWO, etc), with the dates each stage is ready.
It also has the revision date (date schedule was updated).



I want to have the script read the excel file and make a new iCal calendar as follows:

Calendar Name - ProjectONE (ProjectTWO, ProjectTHREE, etc)

then make an entry for each stage in the schedule as follows:
Event Name - StageONE (StageTWO, StageTHREE, etc)
Location - ProjectONE (ProjectTWO, ProjectTHREE, etc)
Date - (the day each stage is ready)
Notes - Revision Date

*again...each of the above information is in different cells of the excel sheet, so it would just pull the information from the designated cells to auto fill in the calendar entires.

The other thing that would be nice, is if the script / app would first check to see if their is an existing calendar with same ProjectONE name, and if yes, delete calendar and replace with new updated schedule information.


I know this is alot, but it sounds simple to me...even though i have no ability to make this

Also, if its easier...it can do this with Entourage calendar....but I prefer iCal.

[macbri]

Your project has two main pieces: get the data out of Excel, and create a calendar with this data.

A quick search on "AppleScript and Excel" returns some example we can draw from. Assume the project name is in cell A1, the stage is in A2, and the target date in A3:

tell application "Microsoft Excel"
	set ProjectName to the value of cell "$A$1" as string
	set FirstTargetName to the value of cell "$B$1" as string
	set FirstTargetDate to the value of cell "$C$1" as date
end tell

Now we've got our info, we search Google again for "AppleScript and iCal" and lo and behold the very first link will take you to a page where you can download an example from Apple for doing just what you want! If we strip out the stuff related to the addressbook and so on, we come up with a simple "make an iCal entry" piece of Applescript:

tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}
	end tell
end tell

Not too complicated at all. Use this to get started. Obviously I'm not about to write the entire program for you, but with these two pieces you should be well on your way.

Good luck,

[mattrstewart]

thank you so much...i will see if i can put this all together correctly...and ill post back with updates

[mattrstewart]

OK....so this is kind of fun...but im in a snag....
I created most of the script based on the information you provided and it looks like this:

tell application "Microsoft Excel"
	set ProjectName to the value of cell "$F$4" as string
	set FirstTargetDate to the value of cell "$D$12" as date
	set FirstTargetName to "All Tooling Patterns in HK" as string
	set SecondTargetDate to the value of cell "$E$12" as date
	set SecondTargetName to "Rough Ceramics" as string
	set ThirdTargetDate to the value of cell "$F$12" as date
	set ThirdTargetName to "Tooling Quote Submitted" as string
	set FourthTargetDate to the value of cell "$G$12" as date
	set FourthTargetName to "Casting Ceramics" as string
	set FifthTargetDate to the value of cell "$H$12" as date
	set FifthTargetName to "Approval of Casting Ceramics" as string
	set SixthTargetDate to the value of cell "$I$12" as date
	set SixthTargetName to "Rough Ceramics to Tempe" as string
	set SeventhTargetDate to the value of cell "$J$12" as date
	set SeventhTargetName to "Tooling PO in HK" as string
	set EighthTargetDate to the value of cell "$L$12" as date
	set EighthTargetName to "Paintmaster in HK" as string
	set NinthTargetDate to the value of cell "$M$12" as date
	set NinthTargetName to "Production Quote Submitted" as string
	set TenthTargetDate to the value of cell "$N$12" as date
	set TenthTargetName to "Production Facility Confirmed" as string
	set EleventhTargetDate to the value of cell "$O$12" as date
	set EleventhTargetName to "1st Shot Samples" as string
	set TwelthTargetDate to the value of cell "$P$12" as date
	set TwelthTargetName to "Blister Layout in HK" as string
	set ThirteenthTargetDate to the value of cell "$Q$12" as date
	set ThirteenthTargetName to "1st Deco Samples" as string
	set FourteenthTargetDate to the value of cell "$R$12" as date
	set FourteenthTargetName to "Mock-up Packaging Samples" as string
	set FifteenthTargetDate to the value of cell "$S$12" as date
	set FifteenthTargetName to "EP Samples" as string
	set SixteenthTargetDate to the value of cell "$T$12" as date
	set SixteenthTargetName to "Packaging Artwork in HK" as string
	set SeventeenthTargetDate to the value of cell "$U$12" as date
	set SeventeenthTargetName to "Production PO in HK" as string
	set EighteenthTargetDate to the value of cell "$V$12" as date
	set EighteenthTargetName to "Release Printing" as string
	set NineteenthTargetDate to the value of cell "$W$12" as date
	set NineteenthTargetName to "Release Injection" as string
	set TwentythTargetDate to the value of cell "$X$12" as date
	set TwentythTargetName to "Release Decoration" as string
	set TwentyFirstTargetDate to the value of cell "$Y$12" as date
	set TwentyFirstTargetName to "PP Samples" as string
	set TwentySecondTargetDate to the value of cell "$Z$12" as date
	set TwentySecondTargetName to "1st Shipment" as string
	set TwentyThirdTargetDate to the value of cell "$K$12" as date
	set TwentyThirdTargetName to "Tooling Start" as string
end tell

tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, notes:"this is a test", allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SecondTargetDate, summary:SecondTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:ThirdTargetDate, summary:ThirdTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FourthTargetDate, summary:FourthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FifthTargetDate, summary:FifthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SixthTargetDate, summary:SixthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SeventhTargetDate, summary:SeventhTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:EighthTargetDate, summary:EighthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:NinthTargetDate, summary:NinthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TenthTargetDate, summary:TenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:EleventhTargetDate, summary:EleventhTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwelthTargetDate, summary:TwelthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:ThirteenthTargetDate, summary:ThirteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FourteenthTargetDate, summary:FourteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:FifteenthTargetDate, summary:FifteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SixteenthTargetDate, summary:SixteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:SeventeenthTargetDate, summary:SeventeenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:EighteenthTargetDate, summary:EighteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:NineteenthTargetDate, summary:NineteenthTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwentythTargetDate, summary:TwentythTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwentyFirstTargetDate, summary:TwentyFirstTargetName, allday event:true, status:confirmed}
		set theEvent to make new event at end of events with properties {start date:TwentySecondTargetDate, summary:TwentySecondTargetName, allday event:true, status:confirmed}
	end tell
	quit
end tell

tell application "iCal"
	launch
end tell

Heres the problem:
I realized a snag....some of the cells that should have dates...will have value as "N/A" or "" (empty cell)......the script stops here and says error" cant create date with entry "N/A"

my thinking is there is 2 routes we can go:
#1 - have a logical statement so that IF value of cell = "" or "N/A", THEN skip entry
OR
#2 - have a script run in beginning that searches all of row 12 from column D to Z, and it should find and replace all values of "" or "N/A" with "0/0/00" (in this option...the entry is still there...but it is made in a far away spot that will pose no harm for me)

[mattrstewart]

...

[mattrstewart]

...