WORKBOOKPATH

Returns the folder path of the active workbook.

'rgeCellRange - (Optional) A cell in the workbook you want the name of.

Public Function WORKBOOKPATH(Optional ByVal rgeCellRange As Range = Nothing) As String
Dim sfullpath As String
    Application.Volatile
    If rgeCellRange Is Nothing Then
        sfullpath = ThisWorkbook.FullName
    Else
        sfullpath = rgeCellRange.Parent.Parent.FullName
    End If

    If (VBA.InStr(sfullpath, "\") = 0) Then
        sfullpath = "'no path'"
    Else
        sfullpath = Left(sfullpath, InStrRev(sfullpath, "\"))
    End If

    WORKBOOKPATH = sfullpath
End Function

You can use the following formula
=LEFT(CELL(Filename",A1),FIND("[",CELL("Filename",A1))-2)&"\""


If you want the folder path of a different open workbook, you can use the optional argument to reference a cell in that open workbook
You can use the WORKBOOKNAME user defined function to return the name of the active workbook
You can use the WORKBOOKFULLPATH user defined function to return the full path and name of the active workbook




© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext