8. Excel Tips - Get file path with an excel formula
This is a quirky one.
You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't.
Fortunately, the function call does have a couple of uses - You can use it to retrieve the file path by using a few text functions around it like so: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
- returns file path (if you have saved the file).
Try it and see !
Also take a look at how to permanently show the file path on the toolbar:
excelexperts.com/Excel-Tips-Display-Excel-File-Path
Training Video on getting the file path:
»
- Nick's blog
- Login or register to post comments
- 174925 reads
Get the full pathed file name
You can however, replace th "[" with "]" and remove the -1 at the end:=LEFT(CELL("filename"),FIND("]",CELL("filename"),1))
and get the full path name and file name:S:\BofARpt\CashReport\[0510_CashRpt.xlsm]
insterting the full path and file name in excel
This is exactly what I was looking for, thank you!
thanks
exactly what I was today looking for!
Help
Ok I tried putting in my filename is all of above and nothing
G:\PHOTOBIO\Patrick\test\[CALFACT.xls]CalFac!$F3
so I have =LEFT(CELL("G:\PHOTOBIO\Patrick\test\[CALFACT.xls]CalFac!$F3"),FIND("]",CELL("G:\PHOTOBIO\Patrick\test\[CALFACT.xls]CalFac!$F3"),1))
triple checked it.
it just comes up value....basically i want to set up a macro that will grab this column then multiply it with another column in current worksheet ...any ideas as at wits end here .
thanks
P
FileName
in: CELL("filename")
"filename" is exactly what you input to the function. Not your actual filename. Excel will calculate the name of the file.
NOTE: You can get the workbook name with this formula:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,(FIND("]",CELL("filename"))+1)-FIND("[",CELL("filename"))-2)
=>
ABC.xls
Get file path
Dear Nick,
Thank you for this really useful tip.
Because I'm curious, I tried both functions.
<<=CELL("filename")>> actually produces the full file-path e.g.
C:\***MESTUFF\**FILES\***MENT\ACCOUNTS\[2009Deposits Cheques Summ 1Sheet.xlsx]1SHEET,
which is exactly what was wanted.
<<=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)>> produces
C:\***MESTUFF\***FILES\***MENT\ACCOUNTS\.
Could this be a characteristic of Excel 2007 under XP
Thanks again!
gaveldot