Header Issue

[cstimart]

Am using the following to attempt to use VBA to create my document header.

fName is a string variable that extracts the .xls/.xlsx/.xlsm from the filename. the pertinent portion of the code that I am using (with no success) is as follows

Activesheet.PageSetup.CenterHeader = "&C&B""Arial""&20" & fName

Help, Please.

 

[cstimart]

My file name currently is "2011 QC Results.xlsm"

After running the macro, the header wants to read as "QC Results"...omitting the "2011 " portion...in addition the "QC" is in a HUGE font in the lower 1/3 of the page.

 

[cstimart]

I believe I have found the issue...still no resolution.

My file name currently is "2011 QC Results.xlsm".....I changed it to "QC Results.xlsm" and everything works fine. Apparently, the code is using the "2011" to max out the font size.

 

[cstimart]

Solved

Needed to put a space after the Font size of 20!

Sub Foo()
    Dim Pos As String
    Dim headerText As String

    With ActiveSheet
        headerText = ThisWorkbook.Name
        Pos = InStr(1, headerText, ".", vbTextCompare) - 1
        headerText = Left(headerText, Pos)
    End With

    ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&20 " & headerText
End Sub