[Simon Tindall]

I use quite alot of excel spreadsheets and print out graphs from them.
It would be really handy that when I print the graphs out the path name i.e.
"c:\my documents\spreadsheet1.xls" would appear in the footer.

I've had a look at some walkthroughs on the net that tell me to go to the footer options and click on the path button, but when I do this its not there.

Help

Simon Tindall

[Advertisements]

VIEW > header or footer

is not there? Do you have dynamic menus (meaning there is an arrow on the bottom of the column that if you put your mouse on it, it expands the menu)

[gerryf]

VIEW > header or footer

is not there? Do you have dynamic menus (meaning there is an arrow on the bottom of the column that if you put your mouse on it, it expands the menu)

[Simon Tindall]

I can get to header and footer via view -> header or footer, when I get there I click on custom footer and get a screen where I can add whatever I want to the footer.

However on the excel help page and all other walkthroughs on the web it says there should be a button (where ive put an arrow) to insert the pathname but i dont have one.

I'm using office 2000, on win 2000

Simon

Attached Thumbnails

• 

[dsenette]

&[Path]&[File] try just putting that in the footer feild

[Simon Tindall]

Just tried that and when I print it out I get this in the footer:

Path]Book1.XLS

So the filename part is working not the pathename.

I've tried doing the same thing in Word 2000 and that works fine.

Edited by Simon Tindall, 23 May 2006 - 09:28 AM.

[gerryf]

that should have worked....are you sure that you typed it in exactly like directed?

&[Path]&[File]

try putting a space before the first &

[Simon Tindall]

I definetly put it in right as i copied and pasted it from your post.

I tried putting a space before the first & and i just got the same result.

Simon Tindall

[gerryf]

try inserting an &[time] infront, just as a test

Also, where is the file stored? Local or network drive? (I am wondering if it the command will not pick up a network path, or path stored too deep)

[dsenette]

Also, where is the file stored? Local or network drive? (I am wondering if it the command will not pick up a network path, or path stored too deep)

fyi - did a test 6 folders deep on my network and the path came up fine (though i can't vouch for simon's network architecture)

[Simon Tindall]

Just tried &[Time]&[File] and i got as expected:

14:49 Book1.XLS.

I clicked in the first set of square brackets and deleted "Time" replacing it with "Path" this brought up what we had before

Path]Book1.XLS

The file i am currently using is saved on my desktop

[gerryf]

weird... is office 2000 completely updated?

[gerryf]

try this

while in the excel workbook , press ALT-f11

In the vba editor, choose THIS WORKBOOK

paste the following

Sub FooterPath()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
End Sub

Save the code and close the editor



Now, go to TOOLS > MACRO > MACROS

choose THISWORKBOOKFOOTERPATH, and hit RUN



Now go to FILE > PRINT PREVIEW



Is the footer there?

[Simon Tindall]

That works!

Great! Thank you very much!

Just one more thing...do i have to go through the same procedure everytime I produce a chart i.e. Alt+F11, add the macro, run the macro etc. or is there a way I can save the macro to my machine so that all i have to do each time I produce a chart in excel is just tools-> Macro -> run.

Thanks a lot

Simon Tindall

[gerryf]

ok, very interesting....that was just a test.

If that worked, so should the other....weird....I wasn't intending for this to be the solution, just a test of Excel functionality...

let me ponder this a moment......so, you want this to occur on every spreadsheet you make? As opposed to just this saved one....should be a way to do that...got to put on my vba hat and think about this....

[gerryf]

Well, that was certainly annoying....



I spent 30 minutes trying to make this thing work, and finally decided to pop open my favorite excel book to look for a a better way to do it, and there was an example on how to do it, that was almost exactly like the one I wrote (but, of course, cleaner). A real slap the head moment (my method was to add the footer at save which might not work in all cases if someone opens an existing file and does not save, this example adds the footer before printing.



Now, for each workbook you create from now on, this will occur WITHOUT running a macro.



drop the attached file into this folder



C:\Program Files\Microsoft Office\OFFICE{#}\XLSTART



For those who care, this is an excel workbook template, and here is the code, added to the THISWORKBOOK module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.LeftFooter = _
ThisWorkbook.FullName
Next sht
End Sub

To modify the existing workbooks, hit the alt +f11 key and add it there, instead of the earlier add so you do not have to run the macro....it will run automatically