Re: Entourage contacts to Excel

[Paul Berkowitz]

Title: Re: Entourage contacts to Excel

On 3/23/06 3:20 PM, "TjL" <[EMAIL PROTECTED]> wrote:

> is it possible to export a list of contacts with this information to
> an Excel file:
>
> Name
> Company Name
> Address (city state zip)
> Work Phone
> Cell Phone
> Home Phone
> etc
>
> ONLY if some field = some criteria?

The way you would filter on some field = some criteria is by a whose clause in Entourage:

tell application "Microsoft Entourage"

    set theContacts to every contact whose company = "Snort Inc."

end tell

If the field is one whose value is text, you might want to consider 'contains' rather than ' = '.


Then you do a repeat loop to extract the properties you need, using the terms as listed in the 'contact' class entry in the Entourage AppleScript dictionary.  You can immediately write the values to a row in an Excel worksheet, or you could write them to a text file delimted by tabs, then when you're done open the .txt file in Excel.

Doing it the first way (to an Excel worksheet, in Excel 2004 which has new AppleScript terms), you'd usually want to start off with a header row, no? So before you start getting the contacts, set that first row. (Add as many fields as you want to the list below.) Getting the address of the column and then splitting off the first part ($F") is a workaround because there's a bad bug that prevents you getting cell 6 of row 1 in a way that you can get its address (AppleScript thinks it's a row, not a cell). At the end, the complicated line constructing the range (row) address uses (i + 1) for the row number since the first row was the header row; you have to add 1. In all cases you need the precise range, not just 'row 1' pr 'row (i + 1)' because that fills all blank cells with "#N/A".

If you just want work address, not default address, change that.


set row1 to {"Name", "Company", "Address", "Work Phone", "Cell Phone", "Home Phone"}
set rr to count row1
tell application "Microsoft Excel"
    set wkBk to make new workbook
   set lastColumnAddress to get address (column rr of active sheet) --"$F:$F"
    set AppleScript's text item delimiters to {":"}
    set lastColumnAddress to text item 1 of lastColumnAddress -- "$F"
    set AppleScript's text item delimiters to {""}
    set value of range ("$A$1:" & lastColumnAddress & "$1") of active sheet to {row1} -- "$A$1:$F$1"
end tell
tell application "Microsoft Entourage"
    set theContacts to every contact whose company = "Snort, Inc."
    repeat with i from 1 to (count theContacts)
        set theContact to item i of my theContacts
       tell theContact
           if default postal address = work then
               set defaultAddress to business address
           else
               set defaultAddress to home address
           end if
           set {theCity, theState, theZip} to defaultAddress's {city, state, zip}
            if theState ≠ "" then
               set theAddress to theCity & ", " & theState
           else
               set theAddress to theCity
           end if
           if theZip ≠ "" then set theAddress to theAddress & " " & theZip
           set theList to {name, company, theAddress, business phone number, mobile phone number, home phone number}
        end tell
       
        tell application "Microsoft Excel"
            set value of range ("$A$" & (i + 1) & ":" & lastColumnAddress & "$" & (i + 1)) of active sheet to {theList}
        end tell
       
    end repeat
end tell


--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

PLEASE always state which version of Microsoft Office you are using - 2004, X  or 2001. It's often impossible to answer your questions otherwise.