We have a useful function in excel called as the CELL function which can be modified to return the sheet name, workbook name and the file path. Lets see how this is done.
Considering that we want this info for the current file. First lets understand the CELL Function. The syntax is =CELL(info_type, [reference]). The CELL Function will return information about the formatting, location or contents of the 1st cell in the reference. The info_type can be the filename, address, color, etc. There is an available list of options in the dropdown which appears so you can choose the required one. The reference is the current workbook which you are using.
The file in this example is located at “B:\”. To obtain the file path, workbook name and sheet name , we use
This will give us the output as B:\[Book1.xlsm]Sheet2 which is the file path + workbook name + sheet name of the workbook in this example.
Now lets move one step ahead. To get the file path only from B:\[Book1.xlsm]Sheet2, we use
The highlighted section will be evaluated first which will find the location of the opening box bracket “[“ in the function. It finds it as location 4.
Our function then narrows down to =MID(CELL(“filename”),1,3). We need the characters starting from the 1st character to the 3rd character (4-1=3) using the mid function. So the output we get will be the file path without the workbook name. And we get the result as “B:\”
Now lets derive the workbook name. Our function is
We need to derive the workbook name from this result B:\[Book1.xlsm]Sheet2. Lets understand how it works.
The highlighted portion will be evaluated first. The workbook name is always in box brackets so we have to search for the opening box bracket [ and closing box bracket ] and our result is within these 2. So this part of the function will return the starting position of the text.
This highlighted portion will evaluate to the last parameter of the MID function which is the number of characters to be extracted from the starting position. So it calculates the position of the ending box bracket which is 15 in this case and deducts the position of the starting box bracket which is 4 and another -1 to exclude the starting box bracket. So the mid function is complete as =MID(CELL(“filename”),5,10) and we get the result as Book1.xlsm as per our example.
Now lets derive the last aspect which is the sheet name. Our function is
It’s a very simple function. First, the highlighted section in this function is evaluated - =MID(CELL(“filename”),,FIND(“]”,CELL(“filename”))+1,255)
This will give us the positing of the ending box bracket + 1 (to exclude the box bracket. So in our example, this results in 15+1 = 16. We directly arrive at the MID function now. It looks like
=MID(CELL(“filename”),16,255). We are asking the function to give us 255 (which is the maximum characters a cell can hold) characters starting from the 16th character of the filename.
So the result we get is Sheet2 as in this example.
To conclude, we can use the same base function =CELL(“filename”) with some enhancements to derive the information we require about the file name.