Excel to iCal script help needed

bluenote · March 1, 2007, 2:25am

I am trying to make a script (mind you I have ZERO knowledge of what I am doing) that will take certain information from my excel documents and make new calendars with events in iCal.

basically…i have a schedule thats made from a template I created in excel. Everyone in the factories and company uses the same template now…so i cant adjust anything on there.

The schedule has the project name, the revision date, the 22 stages of development and the date each stage is ready

I want to make a new calendar in iCal with the name of the Project, and a new event for each of those stages.

here is the code i have so far:

tell application "Microsoft Excel"
	set ProjectName to the value of cell "$F$4" as string
	set FirstTargetDate to the value of cell "$D$12"
	set FirstTargetName to "All Tooling Patterns in HK" as string
	set SecondTargetDate to the value of cell "$E$12"
	set SecondTargetName to "Rough Ceramics" as string
	set ThirdTargetDate to the value of cell "$F$12"
	set ThirdTargetName to "Tooling Quote Submitted" as string
	set FourthTargetDate to the value of cell "$G$12"
	set FourthTargetName to "Casting Ceramics" as string
	set FifthTargetDate to the value of cell "$H$12"
	set FifthTargetName to "Approval of Casting Ceramics" as string
	set SixthTargetDate to the value of cell "$I$12"
	set SixthTargetName to "Rough Ceramics to Tempe" as string
	set SeventhTargetDate to the value of cell "$J$12"
	set SeventhTargetName to "Tooling PO in HK" as string
	set EighthTargetDate to the value of cell "$L$12"
	set EighthTargetName to "Paintmaster in HK" as string
	set NinthTargetDate to the value of cell "$M$12"
	set NinthTargetName to "Production Quote Submitted" as string
	set TenthTargetDate to the value of cell "$N$12"
	set TenthTargetName to "Production Facility Confirmed" as string
	set EleventhTargetDate to the value of cell "$O$12"
	set EleventhTargetName to "1st Shot Samples" as string
	set TwelthTargetDate to the value of cell "$P$12"
	set TwelthTargetName to "Blister Layout in HK" as string
	set ThirteenthTargetDate to the value of cell "$Q$12"
	set ThirteenthTargetName to "1st Deco Samples" as string
	set FourteenthTargetDate to the value of cell "$R$12"
	set FourteenthTargetName to "Mock-up Packaging Samples" as string
	set FifteenthTargetDate to the value of cell "$S$12"
	set FifteenthTargetName to "EP Samples" as string
	set SixteenthTargetDate to the value of cell "$T$12"
	set SixteenthTargetName to "Packaging Artwork in HK" as string
	set SeventeenthTargetDate to the value of cell "$U$12"
	set SeventeenthTargetName to "Production PO in HK" as string
	set EighteenthTargetDate to the value of cell "$V$12"
	set EighteenthTargetName to "Release Printing" as string
	set NineteenthTargetDate to the value of cell "$W$12"
	set NineteenthTargetName to "Release Injection" as string
	set TwentythTargetDate to the value of cell "$X$12"
	set TwentythTargetName to "Release Decoration" as string
	set TwentyFirstTargetDate to the value of cell "$Y$12"
	set TwentyFirstTargetName to "PP Samples" as string
	set TwentySecondTargetDate to the value of cell "$Z$12"
	set TwentySecondTargetName to "1st Shipment" as string
	set TwentyThirdTargetDate to the value of cell "$K$12"
	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
		if (FirstTargetDate is not "N/A" and FirstTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FirstTargetDate, summary:FirstTargetName, allday event:true, status:confirmed}
		end if
		if (SecondTargetDate is not "N/A" and SecondTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SecondTargetDate, summary:SecondTargetName, allday event:true, status:confirmed}
		end if
		if (ThirdTargetDate is not "N/A" and ThirdTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:ThirdTargetDate, summary:ThirdTargetName, allday event:true, status:confirmed}
		end if
		if (FourthTargetDate is not "N/A" and FourthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FourthTargetDate, summary:FourthTargetName, allday event:true, status:confirmed}
		end if
		if (FifthTargetDate is not "N/A" and FifthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FifthTargetDate, summary:FifthTargetName, allday event:true, status:confirmed}
		end if
		if (SixthTargetDate is not "N/A" and SixthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SixthTargetDate, summary:SixthTargetName, allday event:true, status:confirmed}
		end if
		if (SeventhTargetDate is not "N/A" and SeventhTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SeventhTargetDate, summary:SeventhTargetName, allday event:true, status:confirmed}
		end if
		if (EighthTargetDate is not "N/A" and EighthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:EighthTargetDate, summary:EighthTargetName, allday event:true, status:confirmed}
		end if
		if (NinthTargetDate is not "N/A" and NinthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:NinthTargetDate, summary:NinthTargetName, allday event:true, status:confirmed}
		end if
		if (TenthTargetDate is not "N/A" and TenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:TenthTargetDate, summary:TenthTargetName, allday event:true, status:confirmed}
		end if
		if (EleventhTargetDate is not "N/A" and EleventhTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:EleventhTargetDate, summary:EleventhTargetName, allday event:true, status:confirmed}
		end if
		if (TwelthTargetDate is not "N/A" and TwelthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:TwelthTargetDate, summary:TwelthTargetName, allday event:true, status:confirmed}
		end if
		if (ThirteenthTargetDate is not "N/A" and ThirteenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:ThirteenthTargetDate, summary:ThirteenthTargetName, allday event:true, status:confirmed}
		end if
		if (FourteenthTargetDate is not "N/A" and FourteenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FourteenthTargetDate, summary:FourteenthTargetName, allday event:true, status:confirmed}
		end if
		if (FifteenthTargetDate is not "N/A" and FifteenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:FifteenthTargetDate, summary:FifteenthTargetName, allday event:true, status:confirmed}
		end if
		if (SixteenthTargetDate is not "N/A" and SixteenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SixteenthTargetDate, summary:SixteenthTargetName, allday event:true, status:confirmed}
		end if
		if (SeventeenthTargetDate is not "N/A" and SeventeenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:SeventeenthTargetDate, summary:SeventeenthTargetName, allday event:true, status:confirmed}
		end if
		if (EighteenthTargetDate is not "N/A" and EighteenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:EighteenthTargetDate, summary:EighteenthTargetName, allday event:true, status:confirmed}
		end if
		if (NineteenthTargetDate is not "N/A" and NineteenthTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:NineteenthTargetDate, summary:NineteenthTargetName, allday event:true, status:confirmed}
		end if
		if (TwentythTargetDate is not "N/A" and TwentythTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:TwentythTargetDate, summary:TwentythTargetName, allday event:true, status:confirmed}
		end if
		if (TwentyFirstTargetDate is not "N/A" and TwentyFirstTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:TwentyFirstTargetDate, summary:TwentyFirstTargetName, allday event:true, status:confirmed}
		end if
		if (TwentySecondTargetDate is not "N/A" and TwentySecondTargetDate is not "") then
			set theEvent to make new event at end of events with properties {start date:TwentySecondTargetDate, summary:TwentySecondTargetName, allday event:true, status:confirmed}
		end if
	end tell
	quit
end tell

Here is the problems I need help with:

After I the calendar is successfully created, I can only see the new calendar, but no events. After I restart iCal, then the events appear. Is there any way to refresh the calendars or restart iCal? I can get iCal to quit…but not launch again.
Is there a way to have the script check iCal and see if there is a Calendar already there with the same ProjectName. If yes, then it would delete that calendar and make a new one, if no, then it would continue to create a new calendar.
How do I specify the calendar color?
How do I specify the information for “Notes” for the event?

Model: intel iMac 20"
AppleScript: 1.10.7
Browser: Safari 419.3
Operating System: Mac OS X (10.4)

Scott_Champagne · March 1, 2007, 4:56am

Without, yet, getting into your additional questions, here is a way for you to clean up your script.

tell application "Microsoft Excel"
	set ProjectName to the value of cell "$F$4" as string
	
	set targetDateList to the value of range "D12:Z12"
	--assuming that the row names are the row above the data then:
	set targetNameList to the value of range "D11:Z11"
end tell


tell application "iCal"
	activate
	set theCal to make new calendar at end of calendars with properties {title:ProjectName}
	tell theCal
		repeat with x from 1 to 23 --or you can count the items of the list, but the should pretty much always be 23 given your template.
			set currentTargetDate to item x of targetDateList
			set currentTargetName to item x of targetNameList
			addEvent(currentTargetDate, currentTargetName)
		end repeat
	end tell
end tell

on addEvent(targetDate, targetName)
	if (targetDate is not "N/A" and targetDate is not "") then
		make new event at end of events with properties {start date: targetDate, summary:targetName, allday event:true, status:confirmed)
	end if
end addEvent

If I did everything correctly, this should do the same thing that your script did in many less lines of code! ; )

Get your hands on one of the good Applescript books out there to get a good idea of how repeats and handles can make your life so much easier.

bluenote · March 1, 2007, 6:32am

awesome…thanks for the help in cleaning up my code guys…here is what i have:

set the_titles to {"All Tooling Patterns in HK", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling PO in HK", "Tooling Start", "Paintmaster in HK", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "EP Samples", "Packaging Artwork in HK", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}
set tc to count the_titles

tell application "Microsoft Excel"
	set ProjectName to the value of cell "$F$4"
	set ReviseDate to the value of cell "$Z$4"
	tell (get value of range "D12:Z12") to set these_Dates to list 1
end tell

tell application "iCal"
	activate
	if (exists calendar ProjectName) then delete calendar ProjectName
	set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
	
	tell theCal to repeat with i from 1 to tc
		set t_date to item i of these_Dates
		if (t_date is not "N/A" and t_date is not "") then ¬
			make new event at end of events with properties {start date:t_date, summary:item i of the_titles, location:ProjectName, allday event:true, status:confirmed, description:ReviseDate}
	end repeat
	quit
	delay 1
	activate
end tell

BUT…for some reason the ReviseDate isnt showing up…
I want the ReviseDate (which is specified from a specific cell in the document) to show up in 2 locations:

First it should appear in the Calendar description - this is when you click on the calendar, but not on any events, you can see in the info window there is the calendar name (ProjectName) and then a description underneath
Second it should appear in each of the events in the Notes field.

LASTLY…on a side related note, is there any way to send the month view of all my calendars to my desktop picture? I tried PHPiCalendar…and it doesnt work for me now

bluenote · March 2, 2007, 4:55am

awesome…that fixed it…

now what if i want it to print the excel document before creating the ical stuff…

can i just say:

tell "Microsoft Excel"
Print
end tell

??

it doesnt seem to work for me

bluenote · April 10, 2007, 6:14am

How can I add an alarm for 10 days before event for every event that is created?

StefanK · April 10, 2007, 7:36am

Hi,

just add one line to your routine which creates an event


...
tell theCal to repeat with i from 1 to tc
		set t_date to item i of these_Dates
		if (t_date is not "N/A" and t_date is not "") then
			tell (make new event at end of events with properties {start date:t_date, summary:item i of the_titles, location:ProjectName, allday event:true, status:confirmed, description:ReviseDate})
				make new display alarm at end of display alarms with properties {trigger interval:-14400} -- 14400 minutes = 10 days
			end tell
		end if
	end repeat
...

bluenote · April 10, 2007, 8:46am

that doesnt seem to be working…
isnt there a way to add the alarm via properties of the “make new event”?

StefanK · April 10, 2007, 9:06am

No, the different alarms are elements of an event but not a property
This sample script works:

tell application "iCal"
	tell calendar 1
		tell (make new event at end of events with properties {start date:(current date), allday event:true})
			make new display alarm at end of display alarms with properties {trigger interval:-14400} -- 14400 minutes = 10 days
		end tell
	end tell
end tell

bluenote · April 10, 2007, 9:29am

OK…that worked…so now I have this:

Entourage scans incoming emails.
IF subject contains the word Milestone, THEN all attachments are saved to my “Milestone Folder” (on desktop).
I then set a FolderAction for the “Milestone Folder” as follows:


on adding folder items to this_folder after receiving added_items
	tell application "Microsoft Excel"
		repeat with anitem in added_items
			open anitem
			print out active sheet
			run AppleScript Schedule
			close front window
		end repeat
	end tell
end adding folder items to

Note that the “schedule” AppleScript is the following script to import the data and make calendars and new events with alarms in iCal.


set the_titles to {"All Tooling Patterns in HK", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling PO in HK", "Tooling Start", "Paintmaster in HK", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "EP Samples", "Packaging Artwork in HK", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}
set tc to count the_titles

tell application "Microsoft Excel"
	set ProjectName to the value of cell "$F$4"
	set ReviseDate to the value of cell "$Z$4" as string
	tell (get value of range "D12:Z12") to set these_Dates to list 1
	close front window
end tell

tell application "iCal"
	if (exists calendar ProjectName) then delete calendar ProjectName
	set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
	
	tell theCal to repeat with i from 1 to tc
		set t_date to item i of these_Dates
		if (t_date is not "N/A" and t_date is not "") then ¬
			tell (make new event at end of events with properties {start date:t_date, summary:item i of the_titles, location:ProjectName, allday event:true, status:confirmed, description:ReviseDate})
				make new display alarm at end of display alarms with properties {trigger interval:-10080}
			end tell
	end repeat
	quit
end tell

Now…What am I doing wrong in the above codes that is preventing my script “schedule” to run? And…after correcting that…will that make everything automatic for multiple files?

StefanK · April 10, 2007, 9:45am

the syntax to run a script is

run script "path:to:script.scpt" as alias

bluenote · April 10, 2007, 9:52am

I adjusted that…but it still wont run the other script. It prints, then closes window. Do i need to change the “scehdule” script to have a run command in begining?

StefanK · April 10, 2007, 10:18am

why not using a handler instead of a second script like this:

on adding folder items to this_folder after receiving added_items
	tell application "Microsoft Excel"
		repeat with anitem in added_items
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4" as string
			tell (get value of range "D12:Z12") to set these_Dates to list 1
			my Schedule(ProjectName, ReviseDate, these_Dates)
			close front window
		end repeat
	end tell
end adding folder items to

on Schedule(ProjectName, ReviseDate, these_Dates)
	set the_titles to {"All Tooling Patterns in HK", "Rough Ceramics", "Tooling Quote Submitted", "Casting Ceramics", "Approval of Casting Ceramics", "Rough Ceramics to Tempe", "Tooling PO in HK", "Tooling Start", "Paintmaster in HK", "Production Quote Submitted", "Production Facility Confirmed", "1st Shot Samples", "Blister Layout in HK", "1st Deco Samples", "Mock-up Packaging Samples", "EP Samples", "Packaging Artwork in HK", "Production PO in HK", "Release Printing", "Release Injection", "Release Decoration", "PP Samples", "1st Shipment"}
	set tc to count the_titles
	
	tell application "iCal"
		if (exists calendar ProjectName) then delete calendar ProjectName
		set theCal to make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
		
		tell theCal to repeat with i from 1 to tc
			set t_date to item i of these_Dates
			if (t_date is not "N/A" and t_date is not "") then ¬
				tell (make new event at end of events with properties {start date:t_date, summary:item i of the_titles, location:ProjectName, allday event:true, status:confirmed, description:ReviseDate})
					make new display alarm at end of display alarms with properties {trigger interval:-10080}
				end tell
		end repeat
		quit
	end tell
end Schedule

bluenote · April 10, 2007, 4:13pm

thank you soooo much…

it all works flawlessly…or at least for now it does…

next i need to research how to keep iCal calendars alphabetized…which apparently it does not do automatically…