• MacTech Network:
  • Tech Support
  • |
  • MacForge.net
  • |
  • Apple News
  • |
  • Register Domains
  • |
  • SSL Certificates
  • |
  • iPod Deals
  • |
  • Mac Deals
  • |
  • Mac Book Shelf

MAC TECH

  • Home
  • Magazine
    • About MacTech in Print
    • Issue Table of Contents
    • Subscribe
    • Risk Free Sample
    • Back Issues
    • MacTech DVD
  • Archives
    • MacTech Print Archives
    • MacMod
    • MacTutor
    • FrameWorks
    • develop
  • Forums
  • News
    • MacTech News
    • MacTech Blog
    • MacTech Reviews and KoolTools
    • Whitepapers, Screencasts, Videos and Books
    • News Scanner
    • Rumors Scanner
    • Documentation Scanner
    • Submit News or PR
    • MacTech News List
  • Store
  • Apple Expo
    • by Category
    • by Company
    • by Product
  • Job Board
  • Editorial
    • Submit News or PR
    • Writer's Kit
    • Editorial Staff
    • Editorial Calendar
  • Advertising
    • Benefits of MacTech
    • Mechanicals and Submission
    • Dates and Deadlines
    • Submit Apple Expo Entry
  • User
    • Register for Ongoing Raffles
    • Register new user
    • Edit User Settings
    • Logout
  • Contact
    • Customer Service
    • Webmaster Feedback
    • Submit News or PR
    • Suggest an article
  • Connect Tools
    • MacTech Live Podcast
    • RSS Feeds
    • Twitter

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

Introduction  |  Table of Contents

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

Would you like a hard copy
or PDF of this Guide?

You can get a hard copy sent to you
AND download a PDF now ($19.95)
, or

... just download a PDF ($9.95).

Either way, you get a complimentary
MacTech Magazine Subscription

courtesy of the
Microsoft Mac Business Unit


 

  Magazine Reg. Price:  $50.00 value  
  Guide Reg. Price:  $40.00 value  
  You Save:  over $80.00!  

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.



< Previous Page Next Page>
 
MacTech Only Search:
Community Search:

 
 
 

 
 
 
 
 
  • SPREAD THE WORD:
  • Slashdot
  • Digg
  • Del.icio.us
  • Reddit
  • Newsvine
  • Generate a short URL for this page:



MacTech Magazine. www.mactech.com
Toll Free 877-MACTECH, Outside US/Canada: 805-494-9797
MacTech is a registered trademark of Xplain Corporation. Xplain, "The journal of Apple technology", Apple Expo, Explain It, MacDev, MacDev-1, THINK Reference, NetProfessional, Apple Expo, MacTech Central, MacTech Domains, MacNews, MacForge, and the MacTutorMan are trademarks or service marks of Xplain Corporation. Sprocket is a registered trademark of eSprocket Corporation. Other trademarks and copyrights appearing in this printing or software remain the property of their respective holders.
All contents are Copyright 1984-2010 by Xplain Corporation. All rights reserved. Theme designed by Icreon.
 
Nov. 20: Take Control of Syncing Data in Sow Leopard' released
Nov. 19: Cocktail 4.5 (Leopard Edition) released
Nov. 19: macProVideo offers new Cubase tutorials
Nov. 18: S Stardom anounces Safe Capsule, a companion piece for Apple's
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live
Nov. 17: Ableton releases Max for Live