Customising Footers - not doing too well!

W

weeclaire

Hi All

Hoping someone will be able to help me with this query. I am trying to
create a customised footer on a number of excel workbooks. The problem
I have is that the final folder and the file name will change on a
monthly basis. Other than that the path remains the same. I do not
want to have to remember to change the folder and file in the footer on
a monthly basis and was hoping there was a way to create a footer and
allow for this change. I know that it is possible to do the file using

&[File] however when I tried the same theory for the folder and used
&[Folder] these words actually appeared on the footer.

Help! I'm sure there is a way to do this and it is driving me insane
trying to find it.

Thanks

Claire
 
D

Dave Peterson

There are buttons that you can click to get those fields.

In xl2002+, there's a button that looks like a folder that shows:
&[Path]
when you click on it.

Before xl2002, you'll need some other solution.

Maybe John Walkenbach's addin:
http://j-walk.com/ss/excel/files/addpath.htm
to add the path in the header/footer
Hi All

Hoping someone will be able to help me with this query. I am trying to
create a customised footer on a number of excel workbooks. The problem
I have is that the final folder and the file name will change on a
monthly basis. Other than that the path remains the same. I do not
want to have to remember to change the folder and file in the footer on
a monthly basis and was hoping there was a way to create a footer and
allow for this change. I know that it is possible to do the file using

&[File] however when I tried the same theory for the folder and used
&[Folder] these words actually appeared on the footer.

Help! I'm sure there is a way to do this and it is driving me insane
trying to find it.

Thanks

Claire
Click to expand...
 
R

Roger Govier

Hi Claire

Try adding the following macro to a button on your toolbar.
View>Toolbars>Customise>slide down Categories till you get to Macro>drag
the Custom macro button to your toolbar.
Whilst Customise dialogue box is still open, then right click on new
icon>Add macro> footerpath

Sub footerpath()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Activate
ws.PageSetup.RightFooter = ""
ws.PageSetup.RightFooter = ActiveWorkbook.FullName
Next
End Sub

To copy the macro footerpath code as shown above then
Alt + F11 (open Visual Basic Editor)
Ctrl + R (open Project Explorer)
Select your workbook name on left
If no modules exist:
Insert > Module
Paste code in Module
If modules exist:
Double click desired module
Paste code in Module
Clicking the newly added button in your file, will remove anything
entered in your right footer of each worksheet, and will add the current
path and name of file.
Change .rightfooter to .leftfooter or .centerfooter as required.


--
Regards

Roger Govier


"weeclaire" <[email protected]>
wrote in message
news:[email protected]...
 
D

Dave Peterson

You could write your own code or use an addin that writes the code for you.

Take a look at that addin from John Walkenbach.
 
Top