I don’t have much applescript experience but I made a couple of changes which may or may not be of use to you or others.
I added an Activate command for when I don’t have Excel already running.
At the end of the first If statement I changed the -set to none- from theCats to catNames.
I also put in the export of the location field so that I can write whether something is billable or not in there. How are you handling billable vs. non-billable?
I’m going to have a look for a bit of script that asks the user to put in the dates through a dialog box.
Regards Aaron
set startdate to date "Sunday, 1 October 2000 12:00:00 AM"
set EndDate to date "Saturday, 30 December 2000 12:00:00 AM"
set RowNum to 2
set ods to AppleScript's text item delimiters -- the default {""} normally
set AppleScript's text item delimiters to {", "}
tell application "Microsoft Excel"
Activate
end tell
tell application "Microsoft Excel" to set FormulaR1C1 of Range "a1:g1" to {"Start", "End", "Duration", "Subject", "Description", "Category", "Location"}
tell application "Microsoft Entourage"
set searchResults to every event whose start time ¬
is greater than startdate and ¬
start time is less than EndDate
if (count of searchResults) > 1 then
repeat with theEvent in searchResults
set sStart to start time of theEvent
set EEnd to end time of theEvent
set Ssubject to subject of theEvent
set Lloc to location of theEvent
set Desc to content of theEvent
set theCats to category of theEvent
if theCats = {} then
set catNames to "None"
else
set catNames to {}
repeat with aCat in theCats
set end of catNames to name of aCat
end repeat
set catNames to catNames as string
end if
set sRange to "r" & RowNum & "c1:r" & RowNum & "c7"
tell application "Microsoft Excel" to set FormulaR1C1 of Range sRange to {sStart, EEnd, "=24*(rc[-1]-rc[-2])", Ssubject, Desc, catNames, Lloc}
set RowNum to RowNum + 1
end repeat
end if
set AppleScript's text item delimiters to ods -- restore delimeters
end tell
On 2/12/00 5:40 AM, "Bob Greenblatt" <[EMAIL PROTECTED]> wrote:
Paul,
Thanks very much! What a big help. Below is an Apple Script that will place all events within a given date range into an open Excel worksheet. I now can use Entourage to keep track of client billing. I already have a system that filters such a list from the exported calendar items for Palm desktop (which I’m now enthusiastically discarding) in to individual billing records. It’s a simple matter from here to maintain an Excel list of all pertinent categories, and then simply fire this script from Excel and then filter the list from Entourage to generate the invoices. One button push in Excel will get the whole job done.
Here’s the script for anyone who is interested. Many thanks again to Paul Berkowitz who got me over an incredible impasse.
-- Note that these dates are for testing, you’ll have to change them as needed
set startdate to date "Sunday, October 1, 2000 12:00:00 AM"
set EndDate to date "Thursday, November 30, 2000 12:00:00 AM"
set RowNum to 2
set ods to AppleScript's text item delimiters -- the default {""} normally
set AppleScript's text item delimiters to {", "}
tell application "Microsoft Excel" to set FormulaR1C1 of Range "a1:f1" to {"Start", "End", "Duration", ¬ "Subject", "Description", "Category"}
tell application "Microsoft Entourage"
set searchResults to every event whose start time ¬
is greater than startdate and ¬
start time is less than EndDate
if (count of searchResults) > 1 then
repeat with theEvent in searchResults
set sStart to start time of theEvent
set EEnd to end time of theEvent
set Ssubject to subject of theEvent
set Desc to content of theEvent
set theCats to category of theEvent
if theCats = {} then
set theCats to "None"
else
set catNames to {}
repeat with aCat in theCats
set end of catNames to name of aCat
end repeat
set catNames to catNames as string
end if
set sRange to "r" & RowNum & "c1:r" & RowNum & "c6"
tell application "Microsoft Excel" to set FormulaR1C1 of Range sRange to {sStart, ¬
EEnd, "=24*(rc[-1]-rc[-2])", Ssubject, Desc, catNames}
set RowNum to RowNum + 1
end repeat
end if
set AppleScript's text item delimiters to ods -- restore delimeters
end tell
-----------------------
Bob Greenblatt
[EMAIL PROTECTED]
