Utilizing Excel's "Page Setup"

[CE101]

As you may know from my previous postings, I am creating a script that generates an Excel report and it's been a struggle each step of the way...

. http://www.autoitscript.com/forum/index.php?showtopic=116619 Sorting an Excel Worksheet

. http://www.autoitscript.com/forum/index.php?showtopic=116667 Using Excel's "Sort" Object & Methods

. http://www.autoitscript.com/forum/index.php?showtopic=116739 Making Borders in Excel

My next challenge is how to automate Excel's "Page Setup", where among others things, the following things can be changed.... Page orientation, margins, headers/footers, etc, etc.

Not knowing how this is done I turned on the macro recorder to see what's happening behind the scenes when making the following changes...

(1) Changed top and bottom margins from 1 inch (default) to 0.75 inch.

(2) Changed right and left margins from 0.75 inch (default) to 0.25 inch.

(3) Created a custom header with the following features...

.....{a} Left section - date

.....{b} Center section - title ("Test Report")

.....{c} Right section - page#

(4) Created a custom footer with the following features...

.....{a} Left section - filename & worksheet name

(5) PrintTitle Rows - $1:$1 (i.e. display the heading row on each printed page)

Here's the VBA code that gets generated....

With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "&D"
        .CenterHeader = "Winfax Log"
        .RightHeader = "&P of &N"
        .LeftFooter = "&F {&A}"
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With

I tried adapting this code for AutoIT as follows, however I am having trouble with the margin settings...

With $oExcel.ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .PrintArea = ""
        .LeftHeader = "&D"
        .CenterHeader = "Test Report"
        .RightHeader = "&P of &N"
        .LeftFooter = "&F {&A}"
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin =  Application.InchesToPoints(0.25)
        .RightMargin =  Application.InchesToPoints(0.25)
        .TopMargin =  Application.InchesToPoints(0.75)
        .BottomMargin =  Application.InchesToPoints(0.75)
        .HeaderMargin =  Application.InchesToPoints(0.5)
        .FooterMargin =  Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = $xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = $xlPortrait
        .Draft = False
        .PaperSize = $xlPaperLetter
        .FirstPageNumber = $xlAutomatic
        .Order = $xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = $xlPrintErrorsDisplayed
    EndWith

There are two problems with this code.

The first problem is the clause: < .PrintComments = $xlPrintNoComments >

AutoIT says the variable is not declared. (Apparently it doesn't appear in Excel.au3)

So I commented it out as it's probably not critical to my needs.

The other problem is with the following clause and its relatives....

.LeftMargin = Application.InchesToPoints(0.25)

AutoIT recognizes it as being a function, which it is not.

So I (foolishly) changed it to

.LeftMargin = $Application.InchesToPoints(0.25)

At this AutoIT complains that the variable is not declared.

So I tried...

.LeftMargin = .InchesToPoints(0.25)

Still no good.

Finally I tried...

.LeftMargin = 0.25

This time it ran, however instead of giving me margins of 0.25 inches I got margins of 0.0034 .

To get around this I expressed the amount in points as 18, which translates into 0.25 inches.

0.25 / 0.0034 x 0.25 = 18

Certainly it would be easier to express things in inches. So the question is how to get "InchesToPoints" to work in AutoIT.

AND NOW A FEW GENERAL QUESTIONS...

(1) As you can see from my previous postings, getting these Excel "object/methods" to work with AutoIT has been a real struggle. Why can't I just take the VBA code generated by the macro recorder and use it as-is in AutoIT (after prefixing all constants with a dollar sign and prefixing all Excel objects with $oExcel)? I would have assumed that when AutoIT interfaces with the Excel object it passes along all the methods and other parameters as-is (as they appear in our code). If that's the case, why does Excel object to some of our code if it's identical to VBA code? That leads me to suspect that the actual methods are hardcoded in AutoIT and that not everything has been implemented. Am I correct - that it's hardcoded?

(2) Is there any documentation available that will aid me in converting the VBA code to AutoIT?

Any suggestions would be greatly appreciated. Sample code would be even better.

[enaiman]

You have problems with this because nor all constants are being declared in Excel.au3 UDF

Here is a list of all Excel constants, I've saved in a csv format from a bigger sheet with all Office constants. What you have to do is, declare missing constants at the top of your script and you should be fine.

Just rename the file as Constants.csv (couldn't attach *csv files so I had to rename it).

Constants.txt

You can try this code (re-worked your code a bit) - I couldn't find the value of xlPrintErrorsDisplayed so I took that out of your code.

expandcollapse popup

#include <Excel.au3>

$xlPrintNoComments = -4142
$xlPortrait = 1
$xlPaperLetter = 1
$xlDownThenOver = 1


$oExcel = _ExcelBookNew()
With $oExcel.ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .PrintArea = ""
        .LeftHeader = "&D"
        .CenterHeader = "Test Report"
        .RightHeader = "&P of &N"
        .LeftFooter = "&F {&A}"
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin =  $oExcel.Application.InchesToPoints(0.25)
        .RightMargin =  $oExcel.Application.InchesToPoints(0.25)
        .TopMargin =  $oExcel.Application.InchesToPoints(0.75)
        .BottomMargin =  $oExcel.Application.InchesToPoints(0.75)
        .HeaderMargin =  $oExcel.Application.InchesToPoints(0.5)
        .FooterMargin =  $oExcel.Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = $xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = $xlPortrait
        .Draft = False
        .PaperSize = $xlPaperLetter
        .FirstPageNumber = $xlAutomatic
        .Order = $xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
    EndWith

Sleep(20000)
_ExcelBookClose($oExcel)

Edited July 7, 2010 by enaiman

[CE101]

Yes, it works.

.LeftMargin = $oExcel.Application.InchesToPoints(0.25)

Thank you very much.