Discussion Locked

Excel - full path in head and footers

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This questions should really be answered by the TechREpublic Team, as it realtes to an article in the Microsoft Office Resource Book.

I am having difficuilty in implementing the Excel TIP - Inserting full file path in headers and footers. Having followed the instructions, I then go back to the workbook and run the macro. The Macro then display an error message Compile Error - Sub or function not defined. I do not know how to corect this.
The code given is as follows by TechRepublic:-
Tools>Macro>VisualBasicEditor>View>Code
Type following:
Sub PathFooter()
ActiveSheet.PageSetup
LeftFooter=ActiveWorkbook
FullName
EndSub

Go to File/Close

Then run the Macro.
Result Error message as detailed above. can anyone help

6 replies

by dklippert

There are a couple of problems. The macro should read:
Sub PathFooter()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
End Sub
There are only three lines. Notice the periods in the second line.
Also the last line has a space - End Sub

by taarga

Doug
Thank you very much for your speedy reply and help. As you could tell I have not got a clue about VB. Perhaps this is a good time to get clued up :)
Targa

by Former member

I agree with poster 1. If you want a little more info in that path statement you can try

Sub FilePath()
ActiveSheet.PageSetup.LeftFooter =
Application.Path & "\" &
ActiveWorkbook.FullName & "\" &
ActiveCell.Worksheet.NameEnd Sub

The code will find the full path (from drive to worksheet name) and place it in the footer (it has been tested on Excel 97 and 2000 and using Win Me and Win 2000 Pro)

Everything between the Sub and End Sub lines are actually one lineof code.

Ampersands concatenate strings without adding spaces. You can change the path info by changing parts of the left footer string after the equal sign (if you don?t want the worksheet name included in the string just delete everything after ActiveWorkbook.FullName). If you want to change the location of this path info then change the left footer command RightFooter, LeftHeader, or RightHeader. You can see the path info only in print preview or by checking the header and footer information after running the macro.

by taarga

Ramac

Thank you for the expanded version. In view that both replies are worthy the points, I have awarded the points to both of, hope it works.

Thank you again for your response

This discussion is closed to new replies.