Reputation: 381
I want to find an excel or Power query M (not vba) formula to extract the filename of the workbook where this formula is executed. Internet seems to know only one :
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
unfortunately this formula provides only the name of the workbook currently in use, so, if I use a second workbook before executing my function in my first workbook, the name given by this formula will be the name of my second file, which causes terrible mistakes.
Is there a more practical way of doing this ?
Upvotes: 0
Views: 55
Reputation: 50162
If you specify the second (reference) parameter of CELL
, then the formula will be "locked" to the sheet/file in question:
=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1)
This is discussed in the CELL
docs as well as by Chip Pearson here.
Upvotes: 2