0

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 ?

Xodarap
  • 343
  • 1
  • 6
  • 23

1 Answers1

2

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.

BigBen
  • 46,229
  • 7
  • 24
  • 40