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.

 

[jim may]

Try Recording a Macro, this will solve most sytax issues...

 

[cstimart]

Try Recording a Macro, this will solve most sytax issues...

I tried using the record option, with selecting the file name option and it shows as...

ActiveSheet.PageSetup.CenterHeader = "&""Arial,Bold""&20&F"

...also, the ".xlsx" appears in the header.

 

[jim may]

Try

Sub Foo()
Dim fName As String
With ActiveSheet
    fName = ThisWorkbook.Name
    pos = InStr(1, fName, ".", vbTextCompare) - 1
    fName = Left(fName, pos)
    .PageSetup.CenterHeader = "&""Arial,Bold""&20&fName"
End With
End Sub

 

[cstimart]

Try

Sub Foo()
Dim fName As String
With ActiveSheet
    fName = ThisWorkbook.Name
    pos = InStr(1, fName, ".", vbTextCompare) - 1
    fName = Left(fName, pos)
    .PageSetup.CenterHeader = "&""Arial,Bold""&20&fName"
End With
End Sub

That makes the full name appear with the .xlsx and "Name" on the end, too.

 

[jim may]

You are Right, I just now went back to discover that; Sorry.. I'll work with some more to see if I can eliminate the "xls.Name" tacked on.. (???)

 

[cstimart]

I was able to locate this nugget....

 .CenterHeader = IIf(InStr(ThisWorkbook.Name, ".") = 0, ThisWorkbook.Name, Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1))

...so how do I then format the font?

 

[jim may]

Ok, here you go...

Sub Foo()
Dim sName As String
With ActiveSheet
    sName = ThisWorkbook.Name
    pos = InStr(1, sName, ".", vbTextCompare) - 1
    sName = Left(sName, pos)
    .PageSetup.CenterHeader = "&""Arial,Bold""&20" & UCase(Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5))   'use 5 if ".xlsm" or use 4 if ".xls"
End With
End Sub

 

[jim may]

Better still, substitute the text

WorksheetFunction.Proper

instead of the

UCase

 

[cstimart]

I tried both of your suggestions above and it doesn't wanna work for me.