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 84

Printing

Printing is done with the print out command. Normally this is applied to a sheet:

      ActiveSheet.PrintOut

     print out active sheet

Make sure not to leave it at plain print. That will print, in a manner of speaking, because it's the obligatory print command in the Standard Suite. But what it does is bring up the standard Print dialog in the UI and wait for you to click OK. It also misses the possibility of using all the optional parameters for print out that give you a lot of control.

For example, you can specify number of copies and the page ranges. Leaving out the to parameter prints the remainder of the pages to the end, just like in the UI:

      ActiveSheet.PrintOut From:=2, To:=2, Copies:=4

     print out active sheet from 2 to 2 copies 4

If your sheets are grouped, you can print them all by applying print out to the workbook or active window. (In VBA, those are specified as separate Methods.)

      ActiveWorkbook.PrintOut

     print out active workbook

By default, if the header or footer has page numbers, this will cause the page numbers to print continuously from sheet to sheet (that can be changed in preferences):

[An aside: There's another error in the AppleScript dictionary where sheet/window/workbook are listed as constants of a so-called printout options enumeration, for the direct parameter. But this is absurd: you don't print out a sheet constant, you print out an actual sheet object: it's the type (class) sheet that should be listed there, along with type window and type workbook, not an enumeration. We've seen this before. They are just trying to let you know which classes it can be applied to, as if it were a description.

I think the problem is that AppleScript dictionaries may not permit (or did not used to, anyway, before the new sdef dictionaries) multiple classes to be listed. That's why, in older dictionaries, you will often see the type marked as reference, meaning "some application object", or even as anything if one of the allowable types is a basic AppleScript type such as string or number. (It usually does not actually mean "anything at all" but simply has to cover several classes, so "anything" is used as a catch-all type that permits objects of appropriate classes to be direct objects of the command without erroring.) The more usual type seen is reference. Again, it does not mean "any application object whatsoever" but just "multiple types of [unspecified] application classes".

In some cases in the Excel and Word dictionaries, they have listed what looks like several different copies of the same command, e.g., get border, in several different suites. That permits them to list one class per suite as the direct parameter, which I think must be the reason for the multiple listings. But they're cross-referenced, and it works. In other cases, I can see why they might not have wanted to list reference as the type, but that's better than a bogus enumeration. All they would need to do is to state in the description which classes actually can take the command. In the old dictionaries there was not room for longer descriptions, but in the new sdef dictionaries it can be done.]

One thing you cannot do in Office 2004, unfortunately, is print to PDF by script. It certainly was never in the rather ancient VBA model dating back to Office 97/98 and OS 8. It also did not make it into Office 2004 AppleScript. The print to file parameter for print out looks as if it's meant to do just that, but it doesn't work: it prints to paper.

From all the many requests for this feature, I think there's a good chance that it might be implemented in Office 2008 – time will tell.

Page Setup

While Page Setup in VBA is very inefficient, since each parameter requires a separate call to the Print Driver, you can set any parameters that can be set in the GUI dialog:

With ActiveWorkbook.Sheets("Sheet1").PageSetup

   .PrintTitleRows = "$1:$2"

   ActiveSheet.PageSetup.PrintArea = ""  'determine at print time

   .LeftHeader = ""

   .CenterHeader = "My Report"

   .RightHeader = ""

   .LeftFooter = "Page &P of &N"

   .CenterFooter = ""

   .RightFooter = "&Z&F"

   .LeftMargin = Application.InchesToPoints(1)

   .RightMargin = Application.InchesToPoints(1)

   .TopMargin = Application.InchesToPoints(1.5)

   .BottomMargin = Application.InchesToPoints(1.5)

   .HeaderMargin = Application.InchesToPoints(0.5)

   .FooterMargin = Application.InchesToPoints(0.5)

   .PrintHeadings = False

   .PrintGridlines = False

   .PrintComments = xlPrintNoComments

   .PrintQuality = -4