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.

I have added the code for that function to the original post — Lee Blake, Dec 13 '17 at 22:19
If you debug then is there content in `SepList()` ? What's the value of `i` when it errors? — Tim Williams, Dec 13 '17 at 22:53
After investigating a little further, it seems like it only generates the error during file load. Once the file has loaded, I can use the function as above with no issues. I'm wondering if it has something to do with the timing. — Lee Blake, Dec 13 '17 at 22:58

Jim K · Accepted Answer · 2017-12-13T23:34:21.503

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

I had not come across that. However, that gives me the entire path including the file name. I just want the path up to the containing directory. — Lee Blake, Dec 13 '17 at 22:51
that works perfectly! Thank you for such a thorough answer. Not only did I solve this issue, I now understand why it wasn't working. — Lee Blake, Dec 13 '17 at 23:49

Getting an error when getting the path using macro

1 Answers1