VBA: get CentreHeader from sheet

[Archangelos]

I searched in the Internet and I found tons of stuff related to setting Header/Footer (Left, Right, Centre). However, I am trying to do the opposite and I have not succeeded.

What I need is to get the text (ONLY THE TEXT) from CenterHeader.


The text of CenterHeader was "Greece".

I run the following command.
Cells(1, 20) = ActiveSheet.PageSetup.CenterHeader

The A20 cell got the following value:
&"Arial,Έντονα"&UGreece (The word "Έντονα" means Bold in Greek)

This not what I want. I need only the text ("Greece") without the properties of it.

I tried to experiment by changing the command but the debugger «complaint».
Cells(1, 20) = ActiveSheet.PageSetup.CenterHeader.Text FAILED
Cells(1, 20) = ActiveSheet.PageSetup.CenterHeader.Value FAILED
Cells(1, 20) = ActiveSheet.PageSetup.CenterHeader.Caption FAILED

Any ideas?

 

[tonyyy]

Archangelos,

Welcome to the Board.

A couple of questions...
What version of Excel are you using?
What language of Excel are you using?

I ask because when I create a CenterHeader with the word "Greece" with an Arial, Bold font, the text in A20 is &"-,Bold"Greece

That said, the following might get you closer to what you're looking for...

Sub Headder()
    Dim tmp As Variant
    tmp = Split(ActiveSheet.PageSetup.CenterHeader, Chr(34))
    Cells(1, 20) = tmp(UBound(tmp))
End Sub

Cheers,

tonyyy

 

[Archangelos]

1. Excel 2007
2. The language is Greek (it is mentioned in the first post)
3. I tested your code, it did not work perfectly. It was not your fault, it was mine (more details tomorrow, it's 2:16am local time)
4. I will check the split and unbound commands.

Thanks buddy.

 

[Archangelos]

Running the code of tonyyy
When I run the code of tonyyy I got the following:
Initial string: &"Arial,Έντονα"&UGreece
Final string: &UGreece

It seems that I had changed something therefore it was not tonyyy's fault. I had added a single underline to the font.

The new situation
The font name and the bold/italic information are located in the first part of the string that can be removed with the ubound command. But what about the ampersand codes?

I decided to «play» a little with the font properties and run the code several times. Take a look at the various final strings I got.
&S&E&YGreece
&YGreece
&S&E&XGreece


What are the ampersand codes? Well, «playing» a little helped me demystify them (it was not rocket science).
&U: single underline
&E: double underline
&S: strikethrough
&X: superscript
&Y: subscript


After all these, I «played» with the code of tonyyy and i did the following.

Cells(1, 20) = ActiveSheet.PageSetup.CenterHeader
InitialString = Split(ActiveSheet.PageSetup.CenterHeader, Chr(34))
Cells(2, 20) = InitialString
Cells(3, 20) = InitialString(UBound(InitialString))


Cells(4, 20) = Cells(3, 20)


Cells(4, 20) = Replace(Cells(4, 20), "&U", "")
Cells(4, 20) = Replace(Cells(4, 20), "&E", "")
Cells(4, 20) = Replace(Cells(4, 20), "&S", "")
Cells(4, 20) = Replace(Cells(4, 20), "&X", "")
Cells(4, 20) = Replace(Cells(4, 20), "&Y", "")

I made the font bold and italic, with double underline, strikethrough and subscript. I run the code and got the following.
Initial string: &"Arial,Έντονα Πλάγια"&S&E&XGreece
Final string: Greece

 

[tonyyy]

Archangelos - Glad you worked out a solution.

 

[Archangelos]

However, a BIG question remains. How do i get the content of CenterHeader, etc?

There are millions of web pages showing hot to write to CenterHeader but there is no one telling how to read the bloody contents of bloody CenterHeader.

Any idea?