speedy page setup

[Djblois]

Can anybody explain all the parts of this code:

st = "page.setup(,," & _
".25,.25,.6,.6" & _
",0, false,false,false,1,1,true,1,1,false,," & _
".9,.6" & _
", False)"
Application.ExecuteExcel4Macro st

I know which part of the code is the margins but what is the rest of it? I also know this is XML. I just don't know how to use this to setup the rest of the page setup?

[lucas]

Can't use a template?

[Ken Puls]

{snip} I also know this is XML. {snip}
Er... no, it isn't. While you can access XML data in earlier versions, it only became a programming portion of Excel in Excel 2007. What you've come upon is the macro language that was used before VBA. (Hence the name Excel 4 macro).

In the case of PageSetup, I believe that people still use this route as it runs faster than the VBA version. But do you have any specific reason for using it? Or did you inherit it?

The reason I'm asking is specifically around your next question:


I just don't know how to use this to setup the rest of the page setup?

Unless you feel duty bound to stick to what you have, I'd recommend that you use the PageSetup method included in VBA. You can record the code, and it's relatively straight forward to follow. PageSetup recording does tend to generate a lot of garbage code that you can axe out, but I don't recall that it was mystifying when I started (unlike other parts of VBA.)

HTH,

[Djblois]

I have a very large macro that does a lot and it runs much quicker that is why I am using it. Lucas, I can't use a template because I import the info from another program that my company uses.

[JonPeltier]

It's the old Excel 4 macro language, XLM. Spelled almost like XML but nothing at all like it.

I posted a function to handle XLM page setup a while back, which is better documented that what the OP here started with:
http://groups.google.com/group/microsoft.public.excel.charting/browse_frm/thread/7c7bac16c809b007/1dfeea5aca54d31c?lnk=st&q=&rnum=1#1dfeea5aca54d31c

This technique is much faster than the VBA equivalent, so it is worth using, unless you rarely make calls to page setup.

[Ivan F Moala]

Its actually the old XLM language



desc Poss Val
1 head > Hello
2 foot > Testing
3 left > 0.75
4 right > 0.75
5 top > 1
6 bot > 1
7 hdng > FALSE
8 grid > FALSE
9 h_cntr > FALSE
10 v_cntr > FALSE
11 orient > 2
12 paper_size > 9
13 scale > TRUE
14 pg_num > 100
15 pg_order > 1
16 bw_cells > FALSE
17 quality > 300
18 head_margin > 0.5
19 foot_margin > 0.5
20 notes > FALSE
21 draft > FALSE


woops, I see Jon has already posted while I was typing :)

[Ken Puls]

LOL!

Jon, I just googled based on the email notif, and what did I find? This post by you! (http://www.eggheadcafe.com/ng/microsoft.public.excel.charting/Jun2005/post23283696.asp)

' call page setup like this
bSuccess = XLM_PageSetup(<various arguments>)

And the main code (reformatted)
Function XLM_PageSetup(Optional HeaderL As String = "", Optional HeaderC As String = "", _
Optional HeaderR As String = "", Optional FooterL As String = "", _
Optional FooterC As String = "", Optional FooterR As String = "", _
Optional MarginL As Double = 0.5, Optional MarginR As Double = 0.5, _
Optional MarginT As Double = 1, Optional MarginB As Double = 1, _
Optional MarginH As Double = 0.5, Optional MarginF As Double = 0.5, _
Optional PrtRCHead As Boolean = False, Optional PrtGrid As Boolean = False, _
Optional CtrHoriz As Boolean = True, Optional CtrVert As Boolean = False, _
Optional PgOrient As Long = xlLandscape, Optional PaperSize As Integer = 1, _
Optional FitToOne As Boolean = True, Optional PrtScale As Long = 100, _
Optional FitPgsWide As Integer = -1, Optional FitPgsTall As Integer = -1, _
Optional FirstPgNum As Variant = """Auto""", Optional PgOrder As Integer = 1, _
Optional BW As Boolean = False, Optional PrtQual As String = "", _
Optional PrtNotes As Boolean = False, Optional PrtDraft As Boolean = False, _
Optional ChtSize As Long = xlFullPage) As Boolean

Dim sPgSetup As String
Dim sScale As String

If Not ActiveChart Is Nothing Then
sScale = ""
ElseIf FitToOne Then
sScale = "TRUE"
ElseIf FitPgsWide > 0 Or FitPgsTall > 0 Then
sScale = "{" & IIf(FitPgsWide > 0, FitPgsWide, "#N/A") & "," & _
IIf(FitPgsTall > 0, FitPgsTall, "#N/A") & "}"
Else
sScale = CStr(PrtScale)
End If

sPgSetup = """&L" & HeaderL & "&C" & HeaderC & "&R" & HeaderR & ""","
sPgSetup = sPgSetup & """&L" & FooterL & "&C" & FooterC & "&R" & FooterR & ""","
sPgSetup = sPgSetup & MarginL & "," & MarginR & "," & MarginT & "," & MarginB & ","

If ActiveChart Is Nothing Then
sPgSetup = sPgSetup & PrtRCHead & "," & PrtGrid & ","
Else
sPgSetup = sPgSetup & ChtSize & ","
End If

sPgSetup = sPgSetup & CtrHoriz & "," & CtrVert & ","
sPgSetup = sPgSetup & PgOrient & "," & PaperSize & "," & sScale & ","
sPgSetup = sPgSetup & FirstPgNum & ","

If ActiveChart Is Nothing Then sPgSetup = sPgSetup & PgOrder & ","

sPgSetup = sPgSetup & BW & "," & PrtQual & ","
sPgSetup = sPgSetup & MarginH & "," & MarginF & ","

If ActiveChart Is Nothing Then sPgSetup = sPgSetup & PrtNotes & ","

sPgSetup = sPgSetup & PrtDraft
XLM_PageSetup = Application.ExecuteExcel4Macro("PAGE.SETUP(" & sPgSetup & ")")

' True if successful
End Function

I'd already reformatted it, so figured that I might as well submit it here. :)

[Djblois]

So Ken would this work just as fast as the code I am using?

[Ken Puls]

Jon's code is a function to set the XLM macro, so yes. :)