Adding 7 days to a date

bluenote · May 19, 2007, 7:15am

2 problems with below script:

I keep getting this error:
get value of cell “$Z$4”
date “Tuesday, May 15, 2007 12:00:00 AM”
“Can’t make ‘constant e141’ into type number.”
occaisionally i get an error that says TRIDate is not defined.


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
	set TRIDate to ((the value of cell "$S$12") + 7 * days) 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 tell

on Schedule(ProjectName, ReviseDate, these_Dates)
	set the_titles to {"Toolings Due", "Rough Ceramics", "Tooling Quote", "Casting Ceramics", "Approve Castings", "Ceramics to Tempe", "Tooling PO", "Tooling Start", "Paintmasters", "Vendor Quote", "Vendor Confirmed", "1st Shot", "Blister Layout", "1st Deco", "Packaging Mock-ups", "Packaging CD", "EP", "Vendor PO", "Release Printing", "Release Injection", "Release Decoration", "PP", "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:{0, 20000, 50000}, 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 "" and t_date is not "NA" and t_date is not "on hold") then ¬
				make new todo at end of todos with properties {dueDate:t_date, summary:item i of the_titles, status:confirmed, description:ProjectName, url:ReviseDate}
		end repeat
		tell theCal to make new todo at end of todos with properties {dueDate:TRIDate, summary:"TRI CD", status:confirmed, description:ProjectName, url:ReviseDate}
		quit
	end tell
end Schedule

StefanK · May 19, 2007, 7:38am

Hi bluenote,

days is a constant in Excel’s dictionary,
so I would define outside the Excel tell block e.g.
set week to 7 * days
and use the variable week within the tell block
You can avoid “… is not defined” messages by assigning a value to the variable at
the beginning of the script

bluenote · May 19, 2007, 7:46am

so now i can get passed #1 with no error…but i still get an error of “the variable TRIDate is not defined”


tell application "Microsoft Excel"
	print out active sheet
	set ProjectName to the value of cell "$F$4"
	set ReviseDate to the value of cell "$Z$4" as string
	set TRIDate to the value of cell "$S$12" 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 tell

on Schedule(ProjectName, ReviseDate, these_Dates)
	set the_titles to {"Toolings Due", "Rough Ceramics", "Tooling Quote", "Casting Ceramics", "Approve Castings", "Ceramics to Tempe", "Tooling PO", "Tooling Start", "Paintmasters", "Vendor Quote", "Vendor Confirmed", "1st Shot", "Blister Layout", "1st Deco", "Packaging Mock-ups", "Packaging CD", "EP", "Vendor PO", "Release Printing", "Release Injection", "Release Decoration", "PP", "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:{0, 20000, 50000}, 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 "" and t_date is not "NA" and t_date is not "on hold") then ¬
				make new todo at end of todos with properties {dueDate:t_date, summary:item i of the_titles, status:confirmed, description:ProjectName, url:ReviseDate}
		end repeat
		set week to 7 * days
		tell theCal to make new todo at end of todos with properties {dueDate:((TRIDate) + 1 * week), summary:"TRI CD", status:confirmed, description:ProjectName, url:ReviseDate}
		quit
	end tell
end Schedule

StefanK · May 19, 2007, 7:50am

TRIDate is a local variable in the Schedule handler.
The handler itself cannot see the variable in the main script

2 solutions

¢ pass the variable thru the handler call
¢ define TRIDate as global or property

bluenote · May 19, 2007, 7:53am

i have no clue what you just said

remember…im an idiot in applescript still…

StefanK · May 19, 2007, 7:59am

O.K. here the examples

solution 1

...
my Schedule(ProjectName, ReviseDate, these_Dates, TRIDate)
...
on Schedule(ProjectName, ReviseDate, these_Dates, TRIDate)
...

solution 2

at the very beginning of the script insert

property TRIDate : ""

or

global TRIDate

with solution 2 there’s no need to change the handler

bluenote · May 19, 2007, 8:07am

ok…so what you’re saying is that by adding it to the schedule handler…it gets carried over and can be used in the later part of script…

i did that…but now i am back to the same problem of #1 where it says it “Can’t make "Tuesday, July 3, 2007 12:00:00 AM" into type number.”


tell application "Microsoft Excel"
	print out active sheet
	set ProjectName to the value of cell "$F$4"
	set ReviseDate to the value of cell "$Z$4" as string
	set TRIDate to the value of cell "$S$12" as string
	tell (get value of range "D12:Z12") to set these_Dates to list 1
	my Schedule(ProjectName, ReviseDate, these_Dates, TRIDate)
	close front window
end tell

on Schedule(ProjectName, ReviseDate, these_Dates, TRIDate)
	set the_titles to {"Toolings Due", "Rough Ceramics", "Tooling Quote", "Casting Ceramics", "Approve Castings", "Ceramics to Tempe", "Tooling PO", "Tooling Start", "Paintmasters", "Vendor Quote", "Vendor Confirmed", "1st Shot", "Blister Layout", "1st Deco", "Packaging Mock-ups", "Packaging CD", "EP", "Vendor PO", "Release Printing", "Release Injection", "Release Decoration", "PP", "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:{0, 20000, 50000}, 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 "" and t_date is not "NA" and t_date is not "on hold") then ¬
				make new todo at end of todos with properties {dueDate:t_date, summary:item i of the_titles, status:confirmed, description:ProjectName, url:ReviseDate}
		end repeat
		set week to 7 * days
		tell theCal to make new todo at end of todos with properties {dueDate:((TRIDate) + week), summary:"TRI Files", status:confirmed, description:ProjectName, url:ReviseDate}
		quit
	end tell
end Schedule

StefanK · May 19, 2007, 8:15am

of course, because you coerced the date to a string in the main script.
omit as string in the set TRIDate line

Note: iCal understands the days constant, so you dont need to define a week variable
You even can write

tell theCal to make new todo at end of todos with properties {dueDate:(TRIDate + weeks), summary:...

because weeks is the constant 7 * days

bluenote · May 19, 2007, 8:35am

awesome … that totally worked.

you always seem to be the one helping me…i should pay you for the training you are giving me

now i just need to figure out how to define which calendar group to make the new calendar, and if the calendar group exists, then to auto-matically modify the calendar name to add a version # at end of name.

another battle for another day

thanks again stefan

Domminust · May 19, 2007, 8:41am

off topic here, but , Stefan has helped me on alot of stuff too … heh.
there should be a forum rep system :0 kudos to Stefan!

StefanK · May 19, 2007, 9:59am

Thanks, guys