I needed a macro to determine the relative path to a file I want to open. I wanted to store the relative path in a cell. Later I run another macro to read this relative path, open the file and read data. So this macro is just to help me fill in this cell for later use. I hacked this together and I expect there are more elegant ways, I am a real beginner Excel macro writer. If anyone wants they could write a better method. There might be flaws in my method, but so far it is working for me. All my relative files are in a subdirectory of the folder holding the macro that I am running.

I looked around the internet for a solution and did not find one, so I hope by posting others will find this example.

I take the current workbook and get its full and short name, and their lengths. The difference in lengths is the number of characters in the path to this workbook.

I then use an open window call to point to the file who's relative path I want to extract (I need its length as well). Then subtract the difference number above from the file length of the "opened" file, this is the number of characters to keep from the opened file name. Now I use the right command to keep only the characters I want. I add the two .. before the \ to indicate a relative reference.

Sub GetFileName()
' Open file and put relative reference in A12
Dim rng As Range
Dim iLastRow As Integer
Dim iLastCol As Integer
Dim wbkcnt
Dim Fullname As String
Dim fullfil As Integer
Dim shortfil As Integer
Dim fileln As Integer
Dim difference As Integer
Dim Relative As String
Dim Shortname As String


'Next open the text file directly. Dialog window opens first
'Filetoopen is in a subdirectory of current workbook
'want to copy a relative file for the file to open into
'cell A12

filetoOpen = Application _
.GetOpenFilename("All Files (*.*), *.*")

'MsgBox filetoOpen
fileln = Len(filetoOpen)
'fileln - get full length of file to open

Shortname = ThisWorkbook.Name
Fullname = ThisWorkbook.Fullname
fullfil = Len(Fullname)
shortfil = Len(Shortname)
'Fullname - full current file name
'fullfil need length of the full current file
'Shortname - short name, no path
'shortfil - length of short name

difference = fullfil - shortfil
'difference is the length of the path of current workbook, by
'subtracting the short file name from the full file name

lentocopy = (fileln - difference) + 1
'lentocopy is the number of characters to keep from the right
'side of the file to open name for a relative reference, keeps \

Relative = Right(filetoOpen, lentocopy)
'Actually keep from the right side of file to open the lentocopy
'number of characters. Then add the ..
Relative = ".." & Relative
Range("A12") = Relative
End Sub