Inserting File name and path in Excel 2007 Documents

C

Christine

We are trying to figure out how to insert the file name and path in Excel
2007. We are also looking for the same in Word 2007. Is it possible to
create a macro or shortcut for this that we can place in the quick access bar?
 
C

Chip Pearson

You can use the following function called from a worksheet cell, to return
either the filename only (e.g., "SomeFile.xls") or the fully qualified file
name (e.g., "C:\SomePath\SomeFolder\SomeFile.xls").

Function FileName(Optional ShortName As Boolean = False) As String
With Application.Caller.Parent.Parent
FileName = IIf(ShortName, .Name, .FullName)
End With
End Function

You would then call this with

=FileName(TRUE) ' file name only
or either of
=FileName(FALSE) ' full file name
=FileName() ' full file name


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
G

Gord Dibben

Insert the filename and path where?

In a header or footer?

I don't use 2007 but 2002 and 2003 versions had a path and filename icon in
Custom Footers and Headers under print setup.

I can't believe Excel 2007 wouldn't have the same feature.

Macro if you want one.

Sub PathInFooter()
ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & _
ActiveSheet.Name & " " & Application.UserName & " " & Date
End Sub


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top