Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

Create header/footer txt file and link to Excel worksheets

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
marc - 24 Jan 2008 20:18 GMT
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 - 25 Jan 2008 00:17 GMT
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

> Hi all,
>
[quoted text clipped - 14 lines]
>
> --Marc
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.