Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 141

allContactProps is a list of lists, which Excel will use for the value of (two-dimensional) ranges containing rows and columns. We initialize it with a header row containing the names of the fields we want to get – 16 here. If you want fewer, or more, change it. You can use whatever descriptive names you want for the fields here – it's entirely up to you.

The next bit tries to find a relevant email address: since a contact can have several email addresses (as many as you want), this is a bit trickier than getting properties. Normally you'd look for the default email address (which can also be got by just asking for the address of the contact). But in this case, since the only info we want is work-related info, the script attempts to get the first email address of theContact whose label is work.

(Note that a friendly coercion always resolves email address class to its contents property – the actual string address – if you don't ask for another property such as label. This is the only "default property" I have ever come across in AppleScript, mentioned in passing in the Excel chapter. Earlier versions of Entourage, pre-2004, had only this contents property, before label was added. So retaining this useful coercion prevented older scripts from breaking.)

Only if trying to get the first email address fails (on error) do we then try to get the default email address – that will do even if it's a home email – and, if that failthat‘s too (there's no email address for this contact at all), we set the email variable to "" so as not to error later.

Now we can get the other 15 (or whatever) properties we want. Note that using the 'tell theContact' syntax is the only way to do it all in one line as a list and still be able to incorporate the email variable we have just set: if you try it as

   set contactProps to {last name, first name, … email, … } of theContact

it will error, since there is no such email property of contact. tell works since it first tries to resolve words to application keywords – preferably properties of the told object (theContact) – and only if it can't find any such application keyword the compiler then accepts variables.

Excel will need to know the number of rows (i.e., number of contact records) and the number of columns (the number of fields in each record, namely 16 in this example: you can pick any sublist to count, so it might as well be the first one.) And that's Entourage's work done.

Over in Excel, we first make a new workbook, and then have to get a range of the right size before we can set its value (fill it) with our data; otherwise the script will error. It's a peculiarity of Excel 2004 AppleScript that you can refer to ranges only by their "A1" format names: R1C1 format does not work. (Back in Excel X and earlier, you used R1C1 terminology, but it turned out that non-English "localizations" of Excel – French, German, Spanish, etc. – had a bug that returned "R1C1" format although each localization had its local initials, e.g., L1C1 or S1K1 depending on the language, and then didn't understand the R1C1 format of its own results! So now we all use "A1".)

That means that knowing the number of columns does not immediately help – we need to know the letter (character) of the last column in order to specify the range address. The simplest way to get that is to get offset of range (cell) "A1" using the number of columns less 1 (since column A is already counted) and the number of rows less 1 (same reason). That gets not the whole range, but the last cell of the range.

Using get address of that last cell (range) gets the A1 address format that we can use. (Of course we already knew what the row number was, but this gets the whole A1-style address, which we need.) The result is always in "absolute" format "$A$1" so we might as well use the same for the first part of the range address (top left corner) and the lastCell result we just got from get address for the second part (bottom right corner of the range).

Now we can set the value of this range to our list of lists, and the worksheet immediately fills with the data. We can then autofit the range so the columns expand to the width needed to see the content of every cell completely.

It may be that, if you have voluminous notes for some contacts, autofit may make the "Notes" column absurdly wide, even though it is the last column. Some rows may also be absurdly deep as a result of many lines (carriage returns) in the contact Notes, but there's probably not much you can do about that unless you set row height of the sheet to be exactly a particular height. To avoid a huge width, you could, instead of autofitting the entire range, simply autofit every column except the last one: columns are ranges too, so autofit will work on any column individually. Working that out is left as an exercise for the reader.