info("directory") does not work as expected

Question

I have an excel-file and write into a cell info("directory") [I want to know the actual folder-position of my file].

I save this file to an other location and reopen it. But it does not show the correct directory. 

if I

1. select save as
2. save the file
3. overwrite

it suddenly remembers the correct directory

Is there an other way?

thanks

Peter

Answer 1

Try this non-array formula

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

Answer 2

The INFO("Directory") function doesn't return the directory of the workbook. It returns the current default working directory, which is unrelated to the workbook containing the workbook, though the two may be the same. To get the workbook directory of the active workbook, use an array formula like the following:

=LEFT(CELL("filename",A1),MAX(IFERROR(FIND("",CELL("filename",A1),ROW(INDIRECT("1:"&LEN(CELL("filename",A1))))),-1))-1)

The references to A1 can be to any cell in the workbook; it doesn't matter which cell you use. Note that this is an array formula, so you MUST press CTRL SHIFT ENTER

rather than just ENTER when you first enter the formula and whenever

you edit it later. If you do this properly, Excel will display the formula

in the formula bar enclosed in curly braces { }. You don't type in the

braces -- Excel puts them in automatically. The formula will not return the

correct answer if you do not use CTRL SHIFT ENTER. For more information

about array formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.

Note that since the formula uses IFERROR, it will work only in Excel 2007 and later versions.

If you want a VBA approach, use a function like

Function DirName(R As Range) As String

    DirName = R.Worksheet.Parent.Path

End Function

and call it from a worksheet cell as =DirName(A1). Again, the reference to A1 can be any cell; it doesn't matter what cell.