Search notes:

Excel: insert directory of workbook into a cell

The following code snippet is an attempt to demonstrate how it is possible to insert the directory name of an Excel workbook into a cell.

This is possible because range.parent.parent returns the workbook object in which range is located. This object's path property is the directory name that we're interested in.

So, in the cell where we want to display the directory value, we have to insert a formula that calls a user defined function (worksheetDirectory()) that evaluates this expression.

option explicit

sub main() ' {

    activeWorkbook.saveAs _
       fileName   := environ("TEMP") & application.pathSeparator & "test.xlsm" , _
       fileFormat := xlOpenXMLWorkbookMacroEnabled

    cells(1,1).value   = "This workbook is in the directory:"
    cells(2,1).formula = "= worksheetDirectory(a1)"

end sub ' }

function worksheetDirectory(rng as range) as string ' {
    worksheetDirectory = rng.parent.parent.path
end function ' }

Github repository about-MS-Office-object-model, path: /Excel/parent/excelDirectory.bas

When main is executed, it creates worksheet that looks like so:

Index