Adding 7 days to a date

2 problems with below script:

  1. 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.”

  2. 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

Hi bluenote,

  1. 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

  2. You can avoid “… is not defined” messages by assigning a value to the variable at
    the beginning of the script

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

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

i have no clue what you just said :smiley:

remember…im an idiot in applescript still…

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

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

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

awesome … that totally worked.

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

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

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!

Thanks, guys :slight_smile: