How can I automatically print file path on documents

[Guest]

We would like to set up our employees Office docs to automatically add and
print the file path, including file name, as a footer on all Excel and Word
documents.
We think we had this set up at one time, but lost it in an upgrade and now
can't seem to find how to do it.

Thanks.

 

[Dave Peterson]

If you're using xl2002+, you can include the path in the header/footer via
file|Page setup|header/footer tab.

If you're using xl2k and below, you may want to get John Walkenbach's AddPath
addin:
http://j-walk.com/ss/excel/files/addpath.htm

 

[Guest]

Thank you, but I think what you're describing would have to be set up on each
document each time--I was looking to have it happen automatically whenever a
document was saved.

Brenda

 

[Gord Dibben]

Brenda

To change the default Template for all New workbooks...................

Open a new workbook. Customize as you wish.
Note: you can set all sheet footers at the same time by "grouping" the sheets.
Right-click on a sheet tab and "select all sheets". Set up a footer on the
active sheet and will be done to all. Ungroup when setup is complete.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New

Do not use File>New...Blank Workbook or you will get the Excel default workbook.

Existing workbooks are not affected by these settings and would require a macro
or grouping the sheets and make settings as above.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP

 

[Dave Peterson]

Just another option (that I wouldn't use)...

You can create a new workbook and put this in the ThisWorkbook module.

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim sht As Object
For Each sht In Wb.Sheets
sht.PageSetup.CenterFooter = Wb.FullName
Next sht
End Sub


Save this workbook in your XLStart folder. Each time excel starts, this
workbook will open and the workbook_open event will fire. It'll create an
application event that fires each time you print (or print preview) that changes
the center footer to the workbook's full name (including drive, path and
filename).

The reason I wouldn't use this is that you don't get an option to not have this
happen. It'll affect all your workbooks--even the ones that shouldn't have
their footers changed.

I'd either use the stuff built into excel (using Gord's suggestion for new
workbooks) or the addin from John Walkenbach.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

 

[Guest]

Thanks--this looks like just what we were looking to do--I appreciate the
help, all.
Have a great day!

Brenda

 

[Guest]

Dave-
Thanks for the tips. I think we will try Gord's suggestion, as it is
something everyone in the office can easily do. However, I appreciate the
info and links on macros. This is something I would like to learn more
about. I use Excel a lot, and would like to be able to take it to the next
level. I have considered going to one of the seminars that I get flyers on,
but I am afraid I will spend 7 of 8 hours going over stuff I already know
just to get to the macro part.

Thanks again!

Brenda