Using XLM4 Page Setup

zakasnak

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
I visited all of the sites listed in the various posts & downloaded the XLM function help file.

My question is which syntax would I use? I'm wanting to add this to an existing macro (one that currently takes FOREVER). I think syntax 1 for worksheets & macro sheets, but I'm adding to visual basic, so maybe syntax 3 for vb modules?

Here's my existing code, if anyone wants to help me change this to XLM4pageSetUp

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.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
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

*******************************

I've come up with this using the help files:

With UseXLM4pageSetUp()
Application. ExecuteExcel4Macro ("Page.Setup("","",0.75,0.75, 0.75,0.75,FALSE,FALSE,TRUE,FALSE,2,1,100,PAGE_NUMBER,PAGE_ORDER,FALSE,600,0.5,0.5,FALSE,FALSE)")
End With

Can I assume PAGE_NUMBER = 1? I know PAGE_ORDER refers to the "down, then over" statement, would that then be TRUE?

Will this code even work inside the current macro running in place of what I posted above?

HELP?!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
PAGE_ORDER - 1 is down then right, 2 is right then down.

PAGE_NUMBER - whatever the first page is. It's the box for first page number, under the page tab of page setup.
 
Upvote 0
Will this work then?

With
ExecuteExcel4Macro ("Page.Setup("","",0.75,0.75, 0.75,0.75,FALSE,FALSE,TRUE,FALSE,2,1,100,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")
End With

... in place of all of that at the top of this post??
 
Last edited:
Upvote 0
It should, although I don't think you need the With/End With.
 
Upvote 0
okay, I just thought since it was inside other code & that's how the page setup currently is....

Thank you!
 
Upvote 0
You can code like this:

Code: 
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.RightHeader = ""
ActiveSheet.PageSetup.LeftFooter = ""
ActiveSheet.PageSetup.CenterFooter = ""
ActiveSheet.PageSetup.RightFooter = ""

etc

or you can use a With statement like this:

Code: 
With ActiveSheet.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
End With
 
Upvote 0
I don't see why you need the With, doesn't this work?
Rich (BB code): 
Application. ExecuteExcel4Macro ("Page.Setup("","",0.75,0.75, 0.75,0.75,FALSE,FALSE,TRUE,FALSE,2,1,100,PAGE_NUMBER,PAGE_ORDER,FALSE,600,0.5,0.5,FALSE,FALSE)")

iliace

I think the OP's question is to do with speeding up page setup using Excel4 code.:)
 
Last edited:
Upvote 0
I just tried this on my workbook with 197 sheets & it cut the time in more than half... super speedy! Thanks you for your help!
 
Upvote 0
Well, it seems I spoke too soon. After looking at the print preview.... it's not landscaped. I went deeper into the page setup & none of the options I wanted were selected.

In the help file the syntax reads:

PAGE.SETUP(yada, yada, yada ...)
PAGE.SETUP?(yada, yada, yada ...)

What does the question mark do & would my code be:

Application.ExecuteExcel4Macro ("Page.Setup("","",0.75,0.75, 0.75,0.75,FALSE,FALSE,TRUE,FALSE,2,1,100,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")
Application.ExecuteExcel4Macro ("Page.Setup?("","",0.75,0.75, 0.75,0.75,FALSE,FALSE,TRUE,FALSE,2,1,100,1,1,FALSE,600,0.5,0.5,FALSE,FALSE)")
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
PrintArea = "TableName" but visible cells only
Replies
2
Views
589
willow1985
willow1985
J
  • Question Question
Excel VBA error 400 while link to another sheet
Replies
3
Views
607
rollis13
rollis13
M
  • Question Question
Formatting and Charts lost/scrambled when exporting to PDF via macro
Replies
0
Views
679
Moosles
M
L
  • Question Question
Macro recorded print comments anyway
Replies
1
Views
556
sijpie
sijpie
C
  • Solved
VBA to sort Columns from named ranges stopped working
Replies
4
Views
592
cbarryb
C

Forum statistics

Threads
1,224,561
Messages
6,179,522
Members
452,923
Latest member
JackiG

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top