well...

All things printing in Excel need a thorough overhaul
and to be honest I avoid VBA PageSetup as it's very slow.
if you need to set more than 1 property.. or work on
more sheets.

Use the old (xlm) macrocommand PAGE.SETUP instead.
Try it and see it it works..



Public Sub PageSetupXL4M( _
Optional LeftHead As String, Optional CenterHead As String, _
Optional RightHead As String, Optional LeftFoot As String, _
Optional CenterFoot As String, Optional RightFoot As String, _
Optional LeftMarginInches As String, Optional RightMarginInches As
String, _
Optional TopMarginInches As String, Optional BottomMarginInches As
String, _
Optional HeaderMarginInches As String, Optional FooterMarginInches As
String, _
Optional PrintHeadings As String, Optional PrintGridlines As String, _
Optional PrintComments As String, Optional PrintQuality As String, _
Optional CenterHorizontally As String, Optional CenterVertically As
String, _
Optional Orientation As String, Optional Draft As String, _
Optional PaperSize As String, Optional FirstPageNumber As String, _
Optional Order As String, Optional BlackAndWhite As String, _
Optional Zoom As String)
'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead < "" Then head = "&L" & LeftHead
If CenterHead < "" Then head = head & "&C" & CenterHead
If RightHead < "" Then head = head & "&R" & RightHead
If Not head = "" Then head = """" & head & """"
If LeftFoot < "" Then foot = "&L" & LeftFoot
If CenterFoot < "" Then foot = foot & "&C" & CenterFoot
If RightFoot < "" Then foot = foot & "&R" & RightFoot
If Not foot = "" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


DannyG wrote :

When you set the LeftHeader property of the PageSetup object, you can
apply bold styles by adding special formatters.
F.i.: ActiveSheet.PageSetup.LeftHeader = "Dit is &Ueen test&U" &
Chr(10) & "Pagina: &P".

In VBA this works regardless which language version of Excel you have
installed.

However when I call this from an external application through its OLE
interface, then it does matter which language version of Excel you
have installed!

On a Dutch installation &U is ignored, it needs to be &O (=
onderstrepen) and on a German version of Excel it will display the
time two times, as &U stands for 'Uhrzeit'. The help of these
installation contains two tables one for headers entered by VBA and
headers entered manually. It appears that if you try to set headers
and footers in Excel from an external application you need to use
these 'manual' tables.

Is there a way to force Excel to interpret these format codes the
same way when called from VBA?

I am also wandering how it is possible that there is a difference
between VBA and an external app, as both will communicate through the
OLE interface which cannot determine from which environment it was
called? Is there perhaps some preprocessor at work that translates
these values in a transparent way when calling into Excel?

Kind regards,

Danny.