Language portability problems when setting LeftHeader text from external app (=non-VBA)
 

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.

Language portability problems when setting LeftHeader text from external app (=non-VBA)
 

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.

Language portability problems when setting LeftHeader text from external app (=non-VBA)
 

Thanks for your reply, but I can't get it to work. I get an error message
that there is a syntax error in the command. However if I install the
English version of Office then there are no problems at all.

The strange thing is that it also works from VBscript, JScript and C++script
(all based on MS scripting engine), if I choose a non MS based scripting
language or a compiled language that it does not work.

Very strange.

"keepITcool" schreef in bericht
ft.com...
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.