5

I would like to get current document folder path as a cell value. I need it for proper relative reference creation used in a function call OFFSET(INDIRECT(<generated filepath#sheet.cell>))

I am out of luck with google. I found basic function ThisComponent.getURL() that could help.

The nearest solution is via the reference to other file in the same folder with use of function FORMULA() over that cell an then using string operations over the result.

I would expect some simple function like INFO() but I could not find any.

Thanx

urkon
  • 233
  • 1
  • 5
  • 15

3 Answers3

5

Add this

=CELL("filename")

to your cell. It's an absolute path, but you could further manipulate it with the standard text functions.

Documentation reference

artfulrobot
  • 20,637
  • 11
  • 55
  • 81
2

Add this function to the macro library:

Function GetCurrentFolder
    sUrl = ThisComponent.getURL()
    sParts = Split(sUrl, "/")
    ReDim Preserve sParts(0 to UBound(sParts) - 1)
    GetCurrentFolder = Join(sParts, "/")
End Function

Then put this in the spreadsheet formula:

=GETCURRENTFOLDER()
Jim K
  • 12,824
  • 2
  • 22
  • 51
0

The answer above was close to what I wanted but not quite. I wanted to also strip the file:/// off of the directory path, so I modified the function above to the following:

Function GetCurrentFolder
    sUrl = ThisComponent.getURL()
    sParts = Split(sUrl, "/")
    ReDim Preserve sParts(3 to UBound(sParts) - 1)
    GetCurrentFolder = Join(sParts, "/")
End Function

Then you can use the function call like above:

=GETCURRENTFOLDER()
NightriderXP1
  • 1