File name and path

J

John

I know how to insert the file name and path into an excel file. Does
anybody know how to ensure that unique file path is kept when sending the
excel file as an excel attachment without physically typing the file name
and path into a header or footer.

Thanks in advance

John
 
D

Dave Peterson

But once the recipient saves it to their location, the location/filename could
change????

This formula will return the path, workbook name, and sheet name (after the
workbook's been saved):

=CELL("Filename",A1)

If you really don't want it to change, type in the formula and instead of
hitting enter, hit F9, then enter (which evaluates the formula and then enters
it.)
 
F

folderno32

Is it possible to enter in "Last printed on" and this sort of function
like the ones avaiable in Word.

where could I find the VB references for these functions.

Thank you
 
D

Dave Peterson

There are workbook level events that you can tie into.

One of them is the _beforeprint event.

This code goes under the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wks As Worksheet
For Each wks In Me.Worksheets
With wks.PageSetup
.RightFooter = "Last Printed on: " & Format(Date, "mm/dd/yyyy")
End With
Next wks
End Sub

You may want to invest in a book:

A lot of people like John Walkenbach's:
Excel 2003 Power Programming with VBA

You can find a whole list at Debra Dalgleish's site:
http://www.contextures.com/xlbooks.html

Maybe you could print it and visit your local bookstore to see if you can find
one you like.


And if I wanted to learn about events, I'd visit:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
or
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

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

Rich

What if you only want to insert the file name, without the path? I know that =Cell("filename",A1) inserts the file name plus full path.
 
G

Gord Dibben

Rich

From a post by Chip Pearson............

Use
=CELL("filename",A1)
to get the full name, including the sheet name. E.g.,
C:\Temp\[Test.Xls]Sheet1

Use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename"
,A1))-FIND("]",CELL("filename",A1)))
to get just the sheet name
Sheet1


Use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("file
name",A1))-FIND("[",CELL("filename",A1))-1)
to get just the file name
Test.Xls

Use
=LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)))
to get full name without sheet name
C:\Temp\[Test.xls]

Use
=SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)
)),"[",""),"]","")
to get full name without sheet name, and without square brackets
C:\Temp\Test.Xls


Gord Dibben Excel MVP
 
Top