CE101 Posted July 7, 2010 Share Posted July 7, 2010 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. Link to comment Share on other sites More sharing options...
enaiman Posted July 7, 2010 Share Posted July 7, 2010 (edited) 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 robertocm 1 SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script wannabe "Unbeatable" Tic-Tac-Toe Paper-Scissor-Rock ... try to beat it anyway :) Link to comment Share on other sites More sharing options...
CE101 Posted July 8, 2010 Author Share Posted July 8, 2010 Yes, it works. .LeftMargin = $oExcel.Application.InchesToPoints(0.25) Thank you very much. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now