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
9:adbc7966d42d 2006-12-07 back to faxaway
8:89f2688b85fc 2006-12-07 smtp host arg
7:eb4eba0ed22c 2006-12-07 mailfaxes.py starting to work
6:c317d0cb9956 2006-12-07 prepares one PDF doc per officer
5:fe8a79a7ed9f 2006-12-06 faxjob.py iterates over reports
and looks up fax numbers
4:52f42112c287 2006-12-06 better diagnostics
3:862514804543 2006-12-04 officer update mostly working
2:61dfc88ab652 2006-12-04 connecting to FM from py works
1:a1f813e53e79 2006-12-02 HTML/CSS page break test
0:aa2d0cc8a7e9 2006-12-02 save as html, doc export working