ThisWorkbook.FullName/Name issue in Footer

[huntlake]

I am using the following macro to insert data into the Header and Footer of a workbook. If I try to use ThisWorkbook.FullName or ThisWorkbook.Name in the LeftFooter property and either is too long, the Macro is throwing the following error: “Run-Time error ‘1004’: Unable to set the LeftFooter property of the Page Setup class.”
Does anyone have any suggestions?
Sub Insert_Header_Footer()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name
With ws.PageSetup
.CenterHeader = "&""Calibri,Bold""&16" & _
Worksheets("Workbook Reference Data").Range("B3").Value & _
Chr(10) & "&""Calibri,Bold""&20" & _
Worksheets("Workbook Reference Data").Range("B4").Value & _
Chr(10) & "&""Calibri""&12" & _
Worksheets("Workbook Reference Data").Range("B5").Value
.LeftFooter = "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B6").Value & _
Chr(10) & "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B7").Value & _
Chr(10) & ThisWorkbook.FullName
.RightFooter = "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B4").Value & _
Chr(10) & "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B5").Value & _
Chr(10) & "Revised &D &T" & Chr(10) & "Page &P of &N"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
End Sub

 

[Nalani]

Welcome to the Board

First of all, without using code tags, most people will not even look at your code. Me included.

I did a quick assumption of what you want to do. (probably wrong though ? ?)

Try this macro placed in the ThisWorkbook Module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
       '//Inserts the path in the footer of sheet
       With ActiveSheet
             .PageSetup.LeftFooter = "&8" & _
            ThisWorkbook.FullName
            End With
End Sub

Note:
To use code tags:

Type [ code] (without space)

"paste in code from your module"

Type [ /code] (without space)

 

[huntlake]

Thank you for the suggestion. I believe the issue I am having is related to the number of characters I am trying to programmatically insert. If the filename with or without the full path is too long, the macro is throwing an error.

On the code tags - thank you for that suggestion also, I will re-post the code here:

Sub Insert_Header_Footer()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name
With ws.PageSetup
.CenterHeader = "&""Calibri,Bold""&16" & _
Worksheets("Workbook Reference Data").Range("B3").Value & _
Chr(10) & "&""Calibri,Bold""&20" & _
Worksheets("Workbook Reference Data").Range("B4").Value & _
Chr(10) & "&""Calibri""&12" & _
Worksheets("Workbook Reference Data").Range("B5").Value
.LeftFooter = "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B6").Value & _
Chr(10) & "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B7").Value & _
Chr(10) & ThisWorkbook.FullName
.RightFooter = "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B4").Value & _
Chr(10) & "&""Calibri""&8" & _
Worksheets("Workbook Reference Data").Range("B5").Value & _
Chr(10) & "Revised &D &T" & Chr(10) & "Page &P of &N"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
End Sub

 

[Nalani]

What are you actually trying to do/accomplish?

Explain in words without any code, please.

 

[huntlake]

Basically, I am trying to create a template in MS Excel that will allow the user to enter workbook specific information into a sheet within the workbook that will populate the header and footer with a standardized format, so they don't have to setup the header and footer each time they create a new workbook.

On the "data" worksheet, they would enter: Client Name, Title (of the workbook), Project/Subtitle, Author, and Company. The header would be populated with the Client Name, Title, and Project/Subtitle. The left footer will have the Author, Company, and File Name. The right footer will have the Title, Project/Subtitle, Date/Time, and Page # reference.

The problem I am having is when I run the macro, if I use the full path of the filename, or the name of the workbook is too long, it produces an error. I belive it is due to some limitation to the amount of characters that can be programmatically placed into the footer. So, I'm wondering if there is a way I can force it to wrap?

Hope this helps.