9 replies to this topic

[steink]

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]

What does the error popup say?

[steink]

---------------------------
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]

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]

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]

Edit: never mind, still working on it

[kon]

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]

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]

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]

Best Answer

awsome! it work

 

Thanks