Set file name in Header without extension

[Sweeti]

Hello everybody,

I have this code in VBA:
Ws.PageSetup.RightHeader = "&F" & vbCr & "Sweeti"

This code work, but I need to have the file name without the extension.
Also I wish to set the font/size/color for the first line and I don't know how to insert in the above code without errors.

Can you help?

 

[Marc L]

Hi,​

​

try Ws.PageSetup.RightHeader = Left$(Name, InStrRev(Ws.Parent.Name, ".") & vbLf & "Sweeti"​

 

[Sweeti]

Hi,​

​

try Ws.PageSetup.RightHeader = Left$(Name, InStrRev(Ws.Parent.Name, ".") & vbLf & "Sweeti"​

Hi,
I got "Syntax Error" .

This is my full code:

Sub Add_Header_Footer()
Dim sPath As String
Dim Wb As Workbook, Ws As Worksheet
Dim sFile As String
sPath = "C:\Users\Sweeti\Desktop\New Documents\"
sFile = Dir(sPath & "*.xlsx")
Application.ScreenUpdating = False
Do While sFile <> ""
Set Wb = Workbooks.Open(sPath & sFile)
For Each Ws In Wb.Worksheets
Ws.PageSetup.RightHeader = "&F" & vbCr & "Sweeti"
Ws.PageSetup.RightFooter = "This Document" & vbCr & "Approved by:"
Next Ws
Wb.Close SaveChanges:=True
sFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

With this code I get everything the way I want except for the unnecessary extension..

 

[Marc L]

​

Try Ws.Parent.Name rather than Name alone …​

 

[Sweeti]

​

Where ?​

Hmmm... the "Sub Add_Header_Footer()" is yellow marked and the code you sent is in red.

 

[Marc L]

​

In the same time I have edited my previous post so to read & apply …​

 

[Sweeti]

​

In the same time I have edited my previous post so to read & apply …​

I got Compile Error:
Expected: list separator or)

I replaced the line in this:
Ws.PageSetup.RightHeader = Left$(Ws.Parent.Name, InStrRev(Ws.Parent.Name, ".") & vbLf & "Sweeti"

Am I do something wrong?

 

[Marc L]

​

My bad again but obvious according to the error message :​

Ws.PageSetup.RightHeader = Left$(Ws.Parent.Name, InStrRev(Ws.Parent.Name, ".") - 1) & vbLf & "Sweeti"

 

[Sweeti]

​

My bad again but obvious according to the error message :​

Ws.PageSetup.RightHeader = Left$(Ws.Parent.Name, InStrRev(Ws.Parent.Name, ".") - 1) & vbLf & "Sweeti"

Hi,
First of all it's work ! Thanks!

BUT (OMG) I did a mistake, the code suppose to be:
Ws.PageSetup.RightHeader = Left$(Ws.Parent.Name, InStrRev(Ws.Parent.Name, ".") - 1) & vbLf & "Page &[Page] of &[Pages]"

I have 2 problems:
1. It doesn't show the "Page of Pages".
2. The font size is 11 and I wish it to be 10 hence why I asked in first massage how to set the font/size/color.

Can you please ReEdit the code?

 

[Marc L]

An easy way : activate the Macro Recorder and set the header manually in order to get the generated code according to your will …​