VBA Pagesize Excel 2004

[kermy812]

Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Trying to write a macro and need to change my paper size to tabloid. I think I need to do it with sendKeys. Can some one help me with the code?

 

[Bob Greenblatt]

Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel
Trying to write a macro and need to change my paper size to tabloid. I
think I need to do it with sendKeys. Can some one help me with the code?

Please explain EXACTLY what you want to do. SendKeys does NOT work on
the Mac.

 

[kermy812]

I want to be able to change my paper size to tabloid on my active worksheet.

I have a large macro already where I autofilter my different versions in my main file, then separate each version into it's own worksheet within the same file. However, when I do that, each new worksheet is setup for US Letter.

My other Page Setup items (Header, footer, etc.) apply to each worksheet just as I want, before it loops to the next version. I use this at the end of my loop:
    With ActiveSheet.PageSetup
        .LeftHeader = "&A"
        .CenterHeader = "&F"
        .RightHeader = "Page &P"
        .LeftFooter = "&D"
        .CenterFooter = "&24 "
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = -4
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

I basically want to be able to record/capture as if I selected:
File > Page Setup... > Options > Paper Size: Tabloid > (OK), (OK).

 

[Bob Greenblatt]

I want to be able to change my paper size to tabloid on my active
worksheet.

I have a large macro already where I autofilter my different versions in
my main file, then separate each version into it's own worksheet within
the same file. However, when I do that, each new worksheet is setup for
US Letter.

My other Page Setup items (Header, footer, etc.) apply to each worksheet
just as I want, before it loops to the next version. I use this at the
end of my loop:
With ActiveSheet.PageSetup
.LeftHeader = "&A"
.CenterHeader = "&F"
.RightHeader = "Page &P"
.LeftFooter = "&D"
.CenterFooter = "&24 "
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

I basically want to be able to record/capture as if I selected:
File > Page Setup... > Options > Paper Size: Tabloid > (OK), (OK).

Have you checked the object browser for the papersize parameter?

 

[John_McGhie_[MVP]]

It's not in the ActiveSheet object, it's in the Application object. So it
needs to go outside your "With" statement.

Application.pageSetup.papersize = xlPaperTabloid

Hope this helps

I want to be able to change my paper size to tabloid on my active worksheet.

I have a large macro already where I autofilter my different versions in my
main file, then separate each version into it's own worksheet within the same
file. However, when I do that, each new worksheet is setup for US Letter.

My other Page Setup items (Header, footer, etc.) apply to each worksheet just
as I want, before it loops to the next version. I use this at the end of my
loop:
With ActiveSheet.PageSetup
.LeftHeader = "&A"
.CenterHeader = "&F"
.RightHeader = "Page &P"
.LeftFooter = "&D"
.CenterFooter = "&24 "
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

I basically want to be able to record/capture as if I selected:
File > Page Setup... > Options > Paper Size: Tabloid > (OK), (OK).

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[kermy812]

I have no idea how to use that.
I've usually just been recording, editing, copying code from other macros or examples.
Excel Help example (with Legal size paper) tells me I can use this:
Worksheets("Sheet1&quot.PageSetup.PaperSize = xlPaperLegal
But I Get the error: "Run-time error '9'. Subscript out of range"

 

[John_McGhie_[MVP]]

Unless you quote back the text you are commenting on, *I* have no idea what
you are talking about.

"Subscript out of range" means the bit in the brackets does not exist. So:
In your workbook, there is no worksheet named "Sheet1" in the list of work
sheets.

Cheers

I have no idea how to use that.
I've usually just been recording, editing, copying code from other macros or
examples.
Excel Help example (with Legal size paper) tells me I can use this:
Worksheets("Sheet1").PageSetup.PaperSize = xlPaperLegal
But I Get the error: "Run-time error '9'. Subscript out of range"

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[kermy812]

McGhie, As a test, I tried:

Sub Macro1()
Application.PageSetup.PaperSize = xlPaperTabloid
End Sub

I got the error:
Run-Time error '438'. Object doesn't support this property or method.

I'm still kinda new at this. I'm just missing something...

 

[John_McGhie_[MVP]]

Right. Seems to be a bug...

It doesn't work here either, in Excel 2004.

This is the example from the Help:

Sub Macro1()
Worksheets("Sheet1").PageSetup.PaperSize = xlPaperLegal
End Sub

And it gives me an error here too, in Excel 2004.

Nor can I get it to work with the built-in dialog box
Application.Dialogs(xlDialogPageSetup)

Sorry!

Sub Macro1()
Application.PageSetup.PaperSize = xlPaperTabloid
End Sub

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[XinXin]

Thanks for looking at this, John!

We are investigating this issue.

Thanks,
XinXin Liu
Test Lead, Macintosh Business Unit, Microsoft

 

[kermy812]

I wonder if PaperSize isn't supported on the mac? Note the "Omit" line in this example:

http://support.microsoft.com/kb/109205

 

[John_McGhie_[MVP]]

Yes, I would say they ran into the same bug.

And I suspect it is because the function involved does not belong to Excel
but to the Apple OS X Print Subsystem.

I suspect that while we can record and set all the properties on the first
dialog, which is the "Input" to the Print Subsystem, the dialog that appears
when you click "Options" is the "output" from the print subsystem, and we
can neither record nor set the properties there. Chances are, Excel is
simply presenting an OS X window there.

However: The function is documented in the Excel VBA Help, and thus it is
supposed to work from VBA.

I can't get it to work. If XinXin can't get it to work either, there will
be a "please explain..." XinXin is in charge of Testing for Mac Excel


Cheers

I wonder if PaperSize isn't supported on the mac? Note the "Omit" line in this
example:

http://support.microsoft.com/kb/109205

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[kermy812]

That's just terrible for me. Don't have time to test at the moment - Maybe I can insert in my loop:

Application.Dialogs(xlDialogPageSetup).Show

And just manually enter the papersize as my macro runs. Not the greatest solution. Any other ideas?

 

[John_McGhie_[MVP]]

No other ideas: if .Show works, that's what I would do also.

I think we have a wee insect in the works here...

Cheers

That's just terrible for me. Don't have time to test at the moment - Maybe I
can insert in my loop:

Application.Dialogs(xlDialogPageSetup).Show

And just manually enter the papersize as my macro runs. Not the greatest
solution. Any other ideas?

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]