=Cell("filename")

[mims]

We are trying to implement and inter-office policy that requires every worksheet prepared in-house to have the filename (network storage location) on the document. Does anyone know if there is a way to get excel to do that automatically for every new document created. I don't know if it's possible, but any help would be appreciated!
Thanks

[shg]

What's wrong with what you have? You can add that to everyone's standard template (Book.xls).

[mims]

It works, but requires users to remember to enter it on each document. We were wondering if there was a way to program excel to do so automatically. I think what we were hoping is that there is an office manager function that would allow it to be applied to all docs.

[broro183]

hi all,

I think a better way than using a cell("filename") formula is incorporate it as a file footer (or header) that is in every file you open/create.

Shg,
I thought that it could be added to the default file/template too, but when trying it, I can't figure out how to add page footers in - can you do this?

Mims,
The below macros add a header & footer with "p? of ? pages" & "printed: date" and "filepath\file\worksheet tab" to the active sheet of an open file (or each sheet in the file) without changing other customised settings:

Option Explicit

Sub HdrAndFtrForCrntSht()
'To insert a header and footer on current sheet
Application.ScreenUpdating = False
 With ActiveSheet.PageSetup
        .RightHeader = "&8Page &P of &N"
        .LeftFooter = "&8&Z&F\&A!"
        .RightFooter = "&8Printed: &D"
    End With
Application.ScreenUpdating = True
End Sub

Sub HeadersAndFootersForAllWkShts()
' Macro written by Robert Brockett 11/04/2005 - possibly modified from _
'http://209.68.48.119/dmcritchie/excel/pathname.htm?
Application.ScreenUpdating = False
Dim s As Worksheet
      For Each s In ActiveWorkbook.Worksheets
       With s.PageSetup
        .RightHeader = "&8Page &P of &N"
        .LeftFooter = "&8&Z&F\&A!"
        .RightFooter = "&8Printed: &D"
    End With
    Next s
    Application.ScreenUpdating = True
Set s = Nothing
End Sub

These macros could be modified/incorporated into another macro to run through a list of all existing xls files & apply the same standard header & footer but it wouldn't fix the ongoing problem of new files being created.
Also this would wipe/change any detail that is already listed in "rightheader" etc.

Let us know if you want further info on updating existing files en-masse or try a google or two & see what you find eg:

http://www.google.co.nz/search?num=1...+file%22&meta=

hth
Rob

[shg]

Excel can do it automatically, if everyone uses the same Excel template with either VBA to do it when a new workbook is instanced, or the template contains your formula.

Office Manager is new to me.

[deadlyduck]

Excel has a function called =INFO("directory") which displays the path of the current directory or folder- so if you typed that into cell A1 you'd see the path to the storage location of the particular file.

be aware, however, that the Help system advises that this function is used with caution- caveat emptor :-)

[ctmurray]

I got this from somewhere a couple of years ago. It is in module 1 of the workbook. In the worksheet I have the cell call the function aname. When the workbook is opened the cell is filled with the filename. So if you change the filename by renaming using the operating system, the next time it opens it will show the new name.

So cell A1 has the following"
=aname()

Function aName()
    Application.Volatile
    aName = ThisWorkbook.Name
    End Function

The filename does not include any of the path, just the filename. Again I think people would have to use a "template" workbook which has this module.

I did like some of the other suggestions of created headers/footers with the file name as well.