Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

AHK + Excel COM / MSDN - FitToPagesWide



  • Please log in to reply
9 replies to this topic
steink
  • Members
  • 6 posts
  • Last active: Nov 18 2014 05:10 AM
  • Joined: 16 Nov 2014
oWorkbook.Worksheets("Sheet1").PageSetup.Zoom := False
oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesWide := 1
oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesTall := 

http://msdn.microsof...office.15).aspx

 

Why this is not working?

 

Window pop an error about Zoom...

 

I need to make my excel sheet fit in 1 wide only (to make the sheet max width in 1 page)

 

Thanks,



Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012
What does the error popup say?
Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

steink
  • Members
  • 6 posts
  • Last active: Nov 18 2014 05:10 AM
  • Joined: 16 Nov 2014
---------------------------
reader3.ahk
---------------------------
Error:  0x800A03EC - 
Source:		Microsoft Office Excel
Description:	Unable to set the Zoom property of the PageSetup class
HelpFile:		C:\Program Files (x86)\Microsoft Office\Office12\1033\XLMAIN11.CHM
HelpContext:	0

Specifically: Zoom


--->	218: oWorkbook.Worksheets("Sheet1").PageSetup.Zoom := False  
	219: oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesWide := 1  
	220: oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesTall := 1  

error message box



kon
  • Members
  • 1652 posts
  • Last active:
  • Joined: 04 Mar 2013

It looks like you need to set the PrintCommunication property to false before changing the PageSetup properties.
I used the Excel macro recorder to record changing the Width property in Page Layout. This is the resulting VBA code:

Sub Macro1()
'
' Macro1 Macro
'
'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
End Sub

 
So based on that I created the following working example in AHK which sets the Page Layout Scale to 1 page wide:

oExcel := ComObjCreate("Excel.Application")
oExcel.Visible := true
oWorkbook := oExcel.Workbooks.Add
oExcel.PrintCommunication := 0 ;false
oWorkbook.Worksheets("Sheet1").PageSetup.Zoom := 0
oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesWide := 1
oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesTall := 0
oExcel.PrintCommunication := -1 ;true (in VBA)


steink
  • Members
  • 6 posts
  • Last active: Nov 18 2014 05:10 AM
  • Joined: 16 Nov 2014

still getting error:

 

---------------------------
reader3.ahk
---------------------------
Error:  0x80020006 - Nom inconnu.
 
 
Specifically: PrintCommunication
 
Line#
203: oWorkbook.Worksheets("Sheet1").Range("A" ExcelRow).Font.Name := "Courier"  
204: oWorkbook.Worksheets("Sheet1").Range("A" ExcelRow).Font.Size := "15"  
209: Excelrow += 1
212: }
213: oWorkbook.Worksheets("Sheet1").PageSetup.TopMargin := oWorkBook.InchesToPoints("0,4")  
214: oWorkbook.Worksheets("Sheet1").PageSetup.BottomMargin := oWorkBook.InchesToPoints("0,4")  
218: oWorkbook.Visible := true  
---> 220: oWorkbook.Application.PrintCommunication := 0  
222: oWorkbook.Worksheets("Sheet1").PageSetup.Zoom := 0  
223: oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesWide := 1  
224: oWorkbook.Worksheets("Sheet1").PageSetup.FitToPagesTall := 0  
225: oWorkbook.PrintCommunication := -1  
230: Excelrow = 1
234: oWorkbook.ActiveWorkbook.SaveAs(CustomerList2)  
235: oworkbook.Quit  
 
Continue running the script?
---------------------------
Oui   Non   
---------------------------


kon
  • Members
  • 1652 posts
  • Last active:
  • Joined: 04 Mar 2013

Edit: never mind, still working on it



kon
  • Members
  • 1652 posts
  • Last active:
  • Joined: 04 Mar 2013

The code I posted above worked in Excel 2010, but I just tried it on 2007 and it failed. PrintCommunication was apparently added in the 2010 version.

 

In addition to that, looking at the error message you posted...

oWorkbook.PrintCommunication := -1 should be --> oExcel.PrintCommunication := -1 where oExcel is a variable containing a reference to the Application object.

But that will only work if you have 2010.

I'll work on this more when I get a chance. (If someone else wants to jump in, feel free :))



steink
  • Members
  • 6 posts
  • Last active: Nov 18 2014 05:10 AM
  • Joined: 16 Nov 2014

I added the macro from Excel 2007.

 

Yup, no PrintCommunication.

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.708661417322835)
        .RightMargin = Application.InchesToPoints(0.708661417322835)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
End Sub



kon
  • Members
  • 1652 posts
  • Last active:
  • Joined: 04 Mar 2013

The problem is that the Zoom property expects a value between 10-400. When AHK tries to set it to 0, it produces an error. The solution is to tell Excel that you are setting the value to a Bool type. (VT_BOOL is 0xB)

 

I found the way to specify the data type here thanks to jethrow.

 

Try:

oWorkbook.Worksheets("Sheet1").PageSetup.Zoom := ComObj(0xB,0)



steink
  • Members
  • 6 posts
  • Last active: Nov 18 2014 05:10 AM
  • Joined: 16 Nov 2014
✓  Best Answer

awsome! it work :)

 

Thanks