Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
file name and path in worksheet
is there a way to insert the file name and path in a worksheet - i'm not
talking about the footer in the print setup. i am looking for something like the NOW function. thanks -- aprilshowers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
file name and path in worksheet
Enter this formula in the cell where you want the path and filename to appear:
=CELL("filename",A1) Note: This formula will not produce a result until you save the file. Elisabeth "april" wrote: is there a way to insert the file name and path in a worksheet - i'm not talking about the footer in the print setup. i am looking for something like the NOW function. thanks -- aprilshowers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
file name and path in worksheet
The file *must* have been saved at least once before these will work.
For the file name: =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-5) The reference to cell A1 can be *any* cell reference on that sheet. For the path which includes the file name: =LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))) For the path excluding the file name: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1) Note that "filename" is the argument and not a placeholder. Some folks see "filename" and think they need to replace that with the actual file name! Do not replace "filename". -- Biff Microsoft Excel MVP "april" wrote in message ... is there a way to insert the file name and path in a worksheet - i'm not talking about the footer in the print setup. i am looking for something like the NOW function. thanks -- aprilshowers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
file name and path in worksheet
Hi,
You can simplify your use of the CELL function, by removing the second argument, so the longest of the suggested formulas becomes: =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1) And since we call the CELL("filename") funtion four times a range name like F would simplify this to =MID(F,FIND("[",F)+1,FIND("]",F)-FIND("[",F)-1) You can make a name out of the function by choose Insert, Name, Define and entering F in the Names in workbook box and =CELL("filename") in the Refers to box. -- Thanks, Shane Devenshire "april" wrote: is there a way to insert the file name and path in a worksheet - i'm not talking about the footer in the print setup. i am looking for something like the NOW function. thanks -- aprilshowers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
file name and path in worksheet
You can simplify your use of the CELL function, by removing the second
argument As long as you don't have multiple files open at the same time. Using A1 as the 2nd argument "anchors" the formula to the file the formula is entered in. Try it without the 2nd argument then open another file. -- Biff Microsoft Excel MVP "ShaneDevenshire" wrote in message ... Hi, You can simplify your use of the CELL function, by removing the second argument, so the longest of the suggested formulas becomes: =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1) And since we call the CELL("filename") funtion four times a range name like F would simplify this to =MID(F,FIND("[",F)+1,FIND("]",F)-FIND("[",F)-1) You can make a name out of the function by choose Insert, Name, Define and entering F in the Names in workbook box and =CELL("filename") in the Refers to box. -- Thanks, Shane Devenshire "april" wrote: is there a way to insert the file name and path in a worksheet - i'm not talking about the footer in the print setup. i am looking for something like the NOW function. thanks -- aprilshowers |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
file name and path in worksheet
Hi April
You could use the following Function Function Fullpath() Fullpath = ThisWorkbook.FullName End Function Usage =Fullpath() typed in any cell, will return the name and path into the cell where it is entered If the file has not been saved, then it will return just the filename e.g Book1 If you save the function in Personal.xls it will always be available to you in any workbook by typing =Personal.xls!Fullpath() Alternatively, to avoid typing that each time, you could create a defined name in your current workbook InsertnameDefine Name fpath Refers to =Personal.xls!Fullpath() and just use =fpath If you already have a Personal.xls set up, copy the code and paste into a module in the file. If you don't have a Personal.xls, then Open a new workbook Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel Save as Personal.xls in your XLSTART folder -- Regards Roger Govier "april" wrote in message ... is there a way to insert the file name and path in a worksheet - i'm not talking about the footer in the print setup. i am looking for something like the NOW function. thanks -- aprilshowers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying worksheet, path/file access error: '\vbxxx.tmp' | Excel Discussion (Misc queries) | |||
Variabilize File Path or File Name in SumProduct (and Vlookup too) | Excel Worksheet Functions | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
file path | Excel Discussion (Misc queries) | |||
Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) |