Correct Excel error?

bluenote · August 6, 2007, 9:04am

i have a large amount of excel files that cant translated into events in ical…

problem is that many of the excel sheets have cells using 2 digit dates (i.e. 07) and some cells use 4 digits (i.e. 2007).
ical can read the 4 digits ok…but the 2 digits do not get translated.

Excel has an error correction to fix the 2 digit dates to 4 digitis…anyone know how to correct this using applescript?
OR…is there a way to translate this correctly using applescript before it gets made into an event in iCal?

cause right now…those 2 digit dates are getting ignored and are instead made as todays events.

StefanK · August 6, 2007, 9:20am

Hi,

it shouldn’t be a problem, if you could coerce the string to a date
or a correction line like this

set a to "07"
if (count a) is 2 then set a to "20" & a

bluenote · August 6, 2007, 10:21am

im not sure what you mean.

my current script is below. (i know its lng and badly written…im slowly learning though)

all of the dates would need to check to see if it is 2 or 4 digits for the year… and then if it is 2 digits, they need to add the 20 to the begining to make it a 4 digit year before inputting to iCal.


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" as string
			set ReviseDate to the value of cell "$Z$4" as string
			set ToolDate to the value of cell "$K$12"
			set PaintDate to the value of cell "$L$12"
			set VendorDate to the value of cell "$N$12"
			set FirstShot to the value of cell "$O$12"
			set Blister to the value of cell "$P$12"
			set firstDeco to the value of cell "$Q$12"
			set Mockup to the value of cell "$R$12"
			set Artwork to the value of cell "$S$12"
			set TRI to the value of cell "$S$12"
			set EP to the value of cell "$T$12"
			set PODate to the value of cell "$U$12"
			set PPDate to the value of cell "$Y$12"
			my Schedule(ProjectName, ReviseDate, ToolDate, PaintDate, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, TRI, EP, PODate, PPDate)
			close front window
		end repeat
	end tell
end adding folder items to


on Schedule(ProjectName, ReviseDate, ToolDate, PaintDate, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, TRI, EP, PODate, PPDate)
	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 calendar ProjectName
			if (ToolDate is not "N/A" and ToolDate is not "" and ToolDate is not "NA" and ToolDate is not "on hold") then
				make new event at end of events with properties {start date:ToolDate, location:ProjectName, summary:"Tooling PO", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (PaintDate is not "N/A" and PaintDate is not "" and PaintDate is not "NA" and PaintDate is not "on hold") then
				make new event at end of events with properties {start date:PaintDate, location:ProjectName, summary:"Paintmaster", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (VendorDate is not "N/A" and VendorDate is not "" and VendorDate is not "NA" and VendorDate is not "on hold") then
				make new event at end of events with properties {start date:VendorDate, location:ProjectName, summary:"Vendor", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (FirstShot is not "N/A" and FirstShot is not "" and FirstShot is not "NA" and FirstShot is not "on hold") then
				make new event at end of events with properties {start date:FirstShot, location:ProjectName, summary:"1st Shots", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (Blister is not "N/A" and Blister is not "" and Blister is not "NA" and Blister is not "on hold") then
				make new event at end of events with properties {start date:Blister, location:ProjectName, summary:"Layout", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (firstDeco is not "N/A" and firstDeco is not "" and firstDeco is not "NA" and firstDeco is not "on hold") then
				make new event at end of events with properties {start date:firstDeco, location:ProjectName, summary:"1st Deco", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (Mockup is not "N/A" and Mockup is not "" and Mockup is not "NA" and Mockup is not "on hold") then
				make new event at end of events with properties {start date:Mockup, location:ProjectName, summary:"Mock-up", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (Artwork is not "N/A" and Artwork is not "" and Artwork is not "NA" and Artwork is not "on hold") then
				make new event at end of events with properties {start date:Artwork, location:ProjectName, summary:"Packaging", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (TRI is not "N/A" and TRI is not "" and TRI is not "NA" and TRI is not "on hold") then
				make new event at end of events with properties {start date:(TRI + weeks), location:ProjectName, summary:"TRI", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (EP is not "N/A" and EP is not "" and EP is not "NA" and EP is not "on hold") then
				make new event at end of events with properties {start date:EP, location:ProjectName, summary:"EP", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (PODate is not "N/A" and PODate is not "" and PODate is not "NA" and PODate is not "on hold") then
				make new event at end of events with properties {start date:PODate, location:ProjectName, summary:"PO", allday event:true, status:confirmed}
			end if
		end tell
		tell calendar ProjectName
			if (PPDate is not "N/A" and PPDate is not "" and PPDate is not "NA" and PPDate is not "on hold") then
				make new event at end of events with properties {start date:PPDate, location:ProjectName, summary:"PP", allday event:true, status:confirmed}
			end if
		end tell
		quit
	end tell
end Schedule

StefanK · August 6, 2007, 10:43am

this routine is independent of international date format settings

calcDate("01.11.07") -- assuming January 11th, 2007

on calcDate(d)
	if class of d is date then return d
	set {TID, text item delimiters} to {text item delimiters, "."}
	set {mn, dy, yr} to text items of d
	if (count yr) is 2 then set yr to "20" & yr
	set text item delimiters to TID
	tell (current date) to set d to it - (its time)
	tell d to set {its day, its month, its year} to {dy as integer, mn as integer, yr as integer}
	return d
end calcDate

in your country maybe this could be sufficient

on calcDate(d)
	if class of d is date then
		return d
	else
		return date d
	end if
end calcDate

or

on calcDate(d)
	if class of d is date then return d
	set {TID, text item delimiters} to {text item delimiters, "."}
	set {mn, dy, yr} to text items of d
	set text item delimiters to TID
	return date (mn & "/" & dy & "/" & yr)
end calcDate

StefanK · August 6, 2007, 2:04pm

PS: The sample Excel file, you sent me a few weeks ago,
contained date formatted date fields, so further coercion is not needed.

Here is a different version of your script. It doesn’t delete the project calendar,
and creates the events only, if they don’t already exist.

property summaryList : {"Tooling PO", "Paintmaster", "Vendor", "1st Shots", "Layout", "1st Deco", "Mock-up", "Packaging", "TRI", "EP", "PO", "PP"}

on adding folder items to this_folder after receiving added_items
	repeat with anitem in added_items
		tell application "Microsoft Excel"
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4"
			set range_value to item 1 of (get value of range "K12:U12")
			set {ToolDate, PaintDate, blind, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, EP, PODate} to range_value
			set PPDate to the value of cell "$Y$12"
			copy Artwork to TRI
			close front window
		end tell
		tell application "iCal"
			if not (exists calendar ProjectName) then make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
			set theEvents to (get events of calendar ProjectName)
			set dateList to {ToolDate, PaintDate, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, TRI, EP, PODate, PPDate}
			repeat with i from 1 to count summaryList
				if (class of item i of dateList is date) and my NotExistsEvent(theEvents, item i of dateList, item i of summaryList) then
					set d to item i of dateList
					if item i of summaryList is "TRI" then set d to d + weeks
					make new event at end of events of calendar ProjectName with properties {start date:d, location:ProjectName, summary:item i of summaryList, allday event:true, status:confirmed}
				end if
			end repeat
		end tell
	end repeat
	quit application "iCal"
end adding folder items to

on NotExistsEvent(Ev, Da, Su)
	tell application "iCal"
		repeat with i in Ev
			tell contents of i to set {EvDa, EvSu} to {its start date, its summary}
			if Su is "TRI" then
				if EvDa is (Da + weeks) and EvSu is Su then return false
			else
				if EvDa is Da and EvSu is Su then return false
			end if
		end repeat
		return true
	end tell
end NotExistsEvent

bluenote · August 9, 2007, 6:15am

hey stefan…
you probably hate me now that i screwed up all your earlier help on my other thread…but hopefully you arent not TOO mad…

ok…so here is some issues using your latest revision of my crapola:

the date problem still exists, but now with the new code, it doesn’t make the event at all since its not being recorded as a date. I checked the console…and found that for some reason, some cells are being recorded in excel as “06/28/07” instead of “Thursday, June 28, 2007 12:00:00 AM”. The later one works great, but the first one doesnt. I know my template I use is set to work OK…but it seems that other staff or factories will screw with the dates and it will end up like that.

Excel says the error as “text date with 2-digit year”. is there any way to auto correct this? Excel gives options to convert as 19XX or 20XX. Obviously i would want it to choose the 20XX.

It seems my events don’t get replaced using your script. The TRI event definitely doesnt get replaced, and it seems like some of the other dates dont either. they just add the new events. any ideas?
I really wanted to make this script for use with Entourage, but I felt it was too ambitious. This is because with Entourage, I need to figure out how to define an individual calendar, and also how to set the project of a calendar. The biggest issue with this would be that my project name in Entourage might not match the project name in the excel file, so there would need to be a popup window showing the excel file name and give me a list to choose from available projects in entourage. Is this all posiible using applescript?

StefanK · August 9, 2007, 7:01am

No problem, I thought, you want like in your first script only these events to be created, which don’t already exist.
OK, try this, I’ve added a routine, which calculates the dates properly and all events will be deleted and recreated
I don’t use Entourage, so I’m not very familiar with its dictionary

property summaryList : {"Tooling PO", "Paintmaster", "Vendor", "1st Shots", "Layout", "1st Deco", "Mock-up", "Packaging", "TRI", "EP", "PO", "PP"}

on adding folder items to this_folder after receiving added_items
	set added_items to (choose file) as list
	repeat with anitem in added_items
		tell application "Microsoft Excel"
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4"
			set range_value to item 1 of (get value of range "K12:U12")
			set {ToolDate, PaintDate, blind, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, EP, PODate} to range_value
			set PPDate to the value of cell "$Y$12"
			copy Artwork to TRI
			close front window
		end tell
		tell application "iCal"
			if (exists calendar ProjectName) then
				delete events of calendar ProjectName
			else
				make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
			end if
			set dateList to {ToolDate, PaintDate, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, TRI, EP, PODate, PPDate}
			repeat with i from 1 to count summaryList
				set theDate to my calcDate(item i of dateList)
				if class of theDate is date then
					if item i of summaryList is "TRI" then set theDate to theDate + weeks
					make new event at end of events of calendar ProjectName with properties {start date:theDate, location:ProjectName, summary:item i of summaryList, allday event:true, status:confirmed}
				end if
			end repeat
		end tell
	end repeat
	quit application "iCal"
end adding folder items to

on calcDate(d)
	if class of d is date then return d
	if d is "" or d is in {"N/A", "NA", "on hold"} then return false
	set delim to item (((d contains "/") as integer) + 1) of {".", "/"}
	set {TID, text item delimiters} to {text item delimiters, delim}
	try
		set {mn, dy, yr} to text items of d
		if yr as integer < 10 then set yr to (yr as integer) + 2000
		set text item delimiters to TID
		tell (current date) to set d to it - (its time)
		tell d to set {its day, its month, its year} to {dy as integer, mn as integer, yr as integer}
		return d
	on error
		set text item delimiters to TID
		return false
	end try
end calcDate

bluenote · August 28, 2007, 6:40am

ok…so your latest script is flawless…but of course now that it works, i want to change it again.
Basically, before when i was using todos…it was not working well at all, and i was getting very confused. I’d like to have a go at it again…but only if we can get it to input correctly:

using your latest script, how can we change it from events to todos?
BUT…i want to be sure the following:

the calendar is not deleted.
if a todo in the ical calendar is already marked “complete”, then it should not be changed, regardless of the excel information.
todos that are not marked complete already should be updated (if necessary) to match the corresponding date in the excel file
if the TRI date is already marked “completed” in iCal, then it should not be changed. but if TRI date is still incomplete, then it would need to be deleted and updated per the excel file.

I tried several times combining elements of the previous scripts we have worked on…but in the end they were all errors.

StefanK · August 28, 2007, 2:02pm

Hi bluenote,

try this:

property summaryList : {"Tooling PO", "Paintmaster", "Vendor", "1st Shots", "Layout", "1st Deco", "Mock-up", "Packaging", "TRI", "EP", "PO", "PP"}

on adding folder items to this_folder after receiving added_items
	-- set added_items to (choose file) as list
	repeat with anitem in added_items
		tell application "Microsoft Excel"
			open anitem
			print out active sheet
			set ProjectName to the value of cell "$F$4"
			set ReviseDate to the value of cell "$Z$4"
			set range_value to item 1 of (get value of range "K12:U12")
			set {ToolDate, PaintDate, blind, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, EP, PODate} to range_value
			set PPDate to the value of cell "$Y$12"
			copy Artwork to TRI
			close front window
		end tell
		tell application "iCal"
			if not (exists calendar ProjectName) then
				make new calendar at end of calendars with properties {title:ProjectName, color:{37500, 0, 0}, description:ReviseDate}
			end if
			set dateList to {ToolDate, PaintDate, VendorDate, FirstShot, Blister, firstDeco, Mockup, Artwork, TRI, EP, PODate, PPDate}
			repeat with i from 1 to count summaryList
				set theDate to my calcDate(item i of dateList)
				set {theTodo, isCompleted} to my check_Todo(ProjectName, item i of summaryList)
				if class of theDate is date then
					if item i of summaryList is "TRI" then set theDate to theDate + weeks
					if theTodo is false or (item i of summaryList is "TRI" and isCompleted is false) then
						if item i of summaryList is "TRI" then delete theTodo
						make new todo at end of todos of calendar ProjectName with properties {due date:theDate, description:ProjectName, summary:item i of summaryList}
					else
						if isCompleted is false then set due date of theTodo to theDate
					end if
				end if
			end repeat
		end tell
	end repeat
	quit application "iCal"
end adding folder items to

on calcDate(d)
	if class of d is date then return d
	if d is "" or d is in {"N/A", "NA", "on hold"} then return false
	set delim to item (((d contains "/") as integer) + 1) of {".", "/"}
	set {TID, text item delimiters} to {text item delimiters, delim}
	try
		set {mn, dy, yr} to text items of d
		if yr as integer < 10 then set yr to (yr as integer) + 2000
		set text item delimiters to TID
		tell (current date) to set d to it - (its time)
		tell d to set {its day, its month, its year} to {dy as integer, mn as integer, yr as integer}
		return d
	on error
		set text item delimiters to TID
		return false
	end try
end calcDate

on check_Todo(cal, param)
	tell application "iCal"
		tell calendar cal
			repeat with tt in (get todos)
				tell contents of tt
					if summary is param then
						set c to completion date
						try
							c
							return {it, true}
						on error
							return {it, false}
						end try
					end if
				end tell
			end repeat
		end tell
		return {false, false}
	end tell
end check_Todo

bluenote · January 18, 2008, 5:43am

hey all…

so the above script from Stefan was working flawlessly for me…until i upgraded to office 2008 and Leopard.

Now i get this error on running:
“iCal got an error: AppleEvent handler failed.”

Here is the snipit from Event Log:
tell application “iCal”
exists calendar “MLB FanFest 08”
false
make new calendar at end of every calendar with properties {title:“MLB FanFest 08”, color:{37500, 0, 0}, description:date “Tuesday, January 15, 2008 12:00:00 AM”}
“iCal got an error: AppleEvent handler failed.”

any ideas on how to resolve?
cause im at a complete loss without this thing working.

StefanK · January 18, 2008, 8:25am

try this


.
tell application "iCal"
	if not (exists calendar ProjectName) then
		tell (make new calendar at end of calendars with properties {name:ProjectName, description:ReviseDate as text})
			set its color to {37500, 0, 0}
		end tell
	end if
.

bluenote · January 18, 2008, 8:51am

nice to see you are always willing to help me Stefan…

your change got me further down the script so we are getting somewhere…

For a file with existing calendar…i get no errors.
For a file with NO existing calendar…i get down to (note that this happens in same spot every time for this file type):

…
get summary of todo id “C250E43A-A3D0-4CB2-96DB-4923021EE6F5” of calendar id “A2726B97-55FA-4C38-B18A-0120B978B071”
“Vendor”
get summary of todo id “E8F0D211-E2CF-4E35-ADBA-0BEC066F01D4” of calendar id “A2726B97-55FA-4C38-B18A-0120B978B071”
“Packaging”
delete false
“iCal got an error: Can’t make false into type reference.”

StefanK · January 18, 2008, 11:06am

this should do it


.
if item i of summaryList is "TRI" and theTodo is not false then delete theTodo
.