Create header/footer txt file and link to Excel worksheets

[marc]

Hi all,

I work for a company that typically has very large and long
engagements. Many reports/schedules/analysis spreadsheets are created
throughout obviously. Each case usually has its own custom headers/
footers specific to that case. Although they can change numerous
times.


Is there a way to create a simple txt file with a header and footer,
and link the excel doc to that so it reads that file everytime it
opens and creates its headers/footers. This would help so if there is
a change to a header/footer layout during the case, I could simply
edit that one file that every spreadsheet is linked to, oppossed to
opening all files.


Thanks for your help in advance.


--Marc

 

[JLGWhiz]

I don't know how to do what you asked about for a text file,
but this psuedo code will give you an idea of how to accomplish
what you describe. Substitute the actual workbook names into
the array and enter your changes for the header into the
quotes where PROJECT NAME GOES HERE appears and it should
change the header for all the sheets in all the workbooks listed in the array.

Sub HdrFtr()
Dim WB As Array
WB = Array(WB1, WB2, WB3, WB4,...WBn) '...WBn means the number of
workbooks.
For i = 0 To UBound(WB)
For j = 1 To WB(i).Sheets.Count
With WB(i).Worksheets(j).PageSetUp.
.CenterHeader = ""
.CenterHeader = &"Century Gothic" &12 &B "PROJECT _
NAME GOES HERE"
Next j
Next i
End Sub

This can be modified so that a user can make the changes by making an entry
into a pop up input box rather than having to change the code.

Sub HdrFtr()
Dim WB As Array
WB = Array(WB1, WB2, WB3, WB4,...WBn) '...WBn means the number of
workbooks.
newStuff = InputBox("Enter revision to project data", "CHANGE DATA")
For i = 0 To UBound(WB)
For j = 1 To WB(i).Sheets.Count
With WB(i).Worksheets(j).PageSetUp.
.CenterHeader = ""
.CenterHeader = &"Century Gothic" &12 &B & newStuff
Next j
Next i
End Sub