Getting an error when getting the path using macro

Question

After searching online for a solution to getting the current path of the file into a LibreOffice Calc cell, I came across the following macro code:

function GetPath() as string
  GlobalScope.BasicLibraries.loadLibrary("Tools")
  GetPath = Tools.Strings.DirectoryNameoutofPath(ThisComponent.url, "/")
end function

This works until I close the file, then re-open it. When I re-open it, I get the following error message:

Inadmissible value or data type.
Index out of defined range.

This error is generated on the last line of the following function within the Tools Macro library.

Function FileNameoutofPath(ByVal Path as String, Optional Separator as String) as String
Dim i as Integer
Dim SepList() as String
    If IsMissing(Separator) Then
        Path = ConvertFromUrl(Path)
        Separator = GetPathSeparator()      
    End If
    SepList() = ArrayoutofString(Path, Separator,i)
    FileNameoutofPath = SepList(i)
End Function

The code for that function is...

Function ArrayOutOfString(BigString, Separator as String, Optional MaxIndex as Integer)
    Dim LocList() as String
    LocList=Split(BigString,Separator)

    If not isMissing(MaxIndex) then maxIndex=ubound(LocList())  

    ArrayOutOfString=LocList
End Function

I'm not sure why this would generate an error on file load, but continue to work thereafter.

Any idea? Thanks.

Answer 1

There is a better way.

=LEFT(CELL("filename");FIND("#$";CELL("filename"))-1)

Source: https://ask.libreoffice.org/en/question/67271/how-to-automatically-display-file-path-in-a-cell/ Documentation on CELL function: https://help.libreoffice.org/Calc/Information_Functions#CELL

Alternatively, to do it with a macro, assign the following WritePath subroutine to run on the View created event.

function GetPath() as string
    On Error Goto ErrorHandler
    GlobalScope.BasicLibraries.loadLibrary("Tools")
    GetPath = Tools.Strings.DirectoryNameoutofPath(ThisComponent.url, "/")
    ErrorHandler:
end function

Sub WritePath
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oCell = oSheet.getCellRangeByName("A1")
    oCell.setString(GetPath())
End Sub

For an explanation of the problem and how the macro solution works, see https://stackoverflow.com/a/39254907/5100564.

EDIT:

The following expression gives the path without the filename. For it to work, regular expressions must be enabled in formulas under Tools -> Options -> LibreOffice Calc -> Calculate.

=MID(CELL("filename");2;SEARCH("/[^/]*$";CELL("filename"))-1)

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Regular_Expressions_in_Writer