Dan Connolly's tinkering lab notebook

## appscript and office automation

My wife does office work for a local professional and whenever I see her doing work that I know the computer could do for her, I chip in. The end-of-the-month scramble is a clear case: they take client reports, print them out and then manually sort them by officer and fax them out.

Surely I could do better with faxaway, I thought. The only question was: since the reports are in MS Word and the database is in FileMaker Pro, all on a Mac laptop, how much would I have to sell my soul to Apple and Microsoft in the process?

appscript let me drive the process from python. I did quite a bit of HyperTalk programming, but somehow I'm still a bit mystified by AppleScript: which are the language keywords and which are the application vocabulary? The FileMakerAppscriptingOverview made it trivial to crib bits like:

    fm = app("FileMaker Pro")
if not fm.databases[db].exists():
fm.open(FSSpec(path % db))
return fm.databases[db]


FSSPec is deprecated in the Carbon docs, but I never did figure out a replacement.

appscript's integration of AppleScript references into python with its and con is particularly cute, but I pulled a bit of hair out before I figured out how to use it:

def officerFax(db, oName, cName):
# hmm... I'm not sure why this str() is necessary...
officers = db.tables['officers'].records[
its.fields['name'].cellValue == str(oName)]


If FileMaker has a way to use real SQL, I can't find it. Plus, we're running a PowerPC version on an intel MacBook with only 0.5GB of RAM. Emulating FileMaker and MS Word is using a lot of RAM, I suspect. I looked into open source alternatives and found that OpenOffice's Base looks quite capable, and I'm sure oowriter would do the job as an MS Word replacement. I hope the python-uno bridge works on OS X so that I can switch the whole operation over one of these moths.

I did pay a price for not doing it The Apple Way. Technical Q&A QA1018 Using AppleScript to send an email with an attachment shows exactly how to attach a report to a mail message and send it to faxaway. I was able to create and address a mail message from python/appscript, but making the attachment stumped me. After verifying that the AppleScript example does work as advertised, I gave up and wrote a separate mailfaxes.py program that uses python's email and smtplib modules and skips Mail.app altogether. I had to be a little careful since the laptop runs python2.3 and the email modules have been rearranged a bit in python 2.4 and 2.5, but it was reasonably straightforward.

Driving MS Word was, predicably, even klunkier:

TMP="fax_job.htm"
def asHTML(w, dirpath, fname):
"""save current doc as HTML
"""
w.do_Visual_Basic('ActiveDocument.SaveAs FileName:="%s",'
' FileFormat:= wdFormatHTML,'
' HTMLDisplayOnlyOutput:=True' % (TMP,))


Office X has an AppleScript interface, but it's not as rich as the Visual Basic API. I got Word to save as HTML (for processing with BeautifulSoup) but I never did figure out how to tell MS Word which directory to put it in. I wrote a posix2mac() routine to convert /posix/paths to ::mac:paths as used in AppleScript but that didn't help; I ended up with a hard-coded kludge.

Switching syntaxes with do_Visual_Basic is a little bit painful, but when it goes bad the diagnostics are pretty good. "ActivePrinter is read-only on the Macintosh," it said, where w.active_printer = p had just failed silently. The modern VB.NET PrintOut documentation isn't hard to find, but it's a little more tricky to find the 2002 PrintOut docs that are more relevant. I never did get PrintToFile working, nor did I find a way to script the PDF option in Apple's print dialogs. Thank goodness for the Appscript, Word and PDF clue which pointed me to CUPS-PDF for Mac OS X. It worked as advertised, though writing code to wait for a new PDF document in ~/Desktop/cups-pdf/ was tricky; we sent a number of reports to the wrong place due to a timing bug.

The Python Bindings for Quartz 2D rock; composing fax cover pages couldn't be easier than this:

htmltxt = coverHTML(oName, fax, subject, pages)
ctx.drawHTMLTextInRect(
CG.CGDataProviderCreateWithString(htmltxt),
pageRect.inset(72, 72))


and concatenating several PDFs into one was similarly straightforward. It doesn't hurt that faxing is Apple's example application.

For reference:

hh-fax2\$ hg log --template '#rev#:#node|short#
#date|shortdate# #desc|firstline|strip#\n'
11:7b497e5881d8 2006-12-07 fixed nasty timing bug with PDF
virtual printer
10:5fbd62cf7025 2006-12-07 fixed SMTP details