PAGE.SETUP header format query

[pyarram]

Hi

I'm trying to insert a carriage return into the page header using the
PAGE.SETUP Excel 4 Macro. I'd be grateful for any pointers.

I was originally using the .pagesetup function but it was taking too
long to communicate with our network printers so I switched to using
John Green's PAGE.SETUP code which is quicker and works fine except for
the issue with the carriage return.

This is the original line of code I'm trying to replicate with the
page.setup macro:
wTargetwks.PageSetup.CenterHeader = "&F" & vbCr & "&A"

The page.setup equivalent I'm using is:
head = """&C&F" & vbCr & "&C&A"""

When I use the .pagesetup version excel correctly recognises the
carriage return and puts the tab name underneath the workbook name.
When I use the page.setup version excel doesn't recognise the carriage
return and both names are superimposed on top of each other on the same
line.

If you then go into Page Setup in Excel and view the custom header
Excel then 'recognises' the carriage return and if you click on okay
the header is correctly displayed.

Does anyone know of a way around this problem? The only work around I
can think of is to put the worksheet name and tab name in different
parts of the header, however I need to maintain the existing worksheet
format if I can.

Any help would be most appreciated.

Thanks

Paul

 

[Jim Rech]

This works for me:

Application.ExecuteExcel4Macro "PAGE.SETUP(""&C&F" & vbCr & "&A"")"


--
Jim
message |
| Hi
|
| I'm trying to insert a carriage return into the page header using the
| PAGE.SETUP Excel 4 Macro. I'd be grateful for any pointers.
|
| I was originally using the .pagesetup function but it was taking too
| long to communicate with our network printers so I switched to using
| John Green's PAGE.SETUP code which is quicker and works fine except for
| the issue with the carriage return.
|
| This is the original line of code I'm trying to replicate with the
| page.setup macro:
| wTargetwks.PageSetup.CenterHeader = "&F" & vbCr & "&A"
|
| The page.setup equivalent I'm using is:
| head = """&C&F" & vbCr & "&C&A"""
|
| When I use the .pagesetup version excel correctly recognises the
| carriage return and puts the tab name underneath the workbook name.
| When I use the page.setup version excel doesn't recognise the carriage
| return and both names are superimposed on top of each other on the same
| line.
|
| If you then go into Page Setup in Excel and view the custom header
| Excel then 'recognises' the carriage return and if you click on okay
| the header is correctly displayed.
|
| Does anyone know of a way around this problem? The only work around I
| can think of is to put the worksheet name and tab name in different
| parts of the header, however I need to maintain the existing worksheet
| format if I can.
|
| Any help would be most appreciated.
|
| Thanks
|
| Paul
|
|
| --
| pyarram
| ------------------------------------------------------------------------
| pyarram's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=32404
| View this thread: http://www.excelforum.com/showthread.php?threadid=521690
|

 

[pyarram]

Jim

Thanks that worked.

The extra &C I'd added before the &A must have been confusing it?

Application.ExecuteExcel4Macro "PAGE.SETUP(""&C&F" & vbCr & "&*C*A"")"

Thanks

Paul