Questions similar to this appear on the MicroStation Programming Forum.

Q How do I compute a relative path between two folders using VBA?

A There's an MDL function mdlFile_findRelativePath that accomplishes that for us. We must first declare the function so that VBA knows where to find it. Copy this declaration to the top of your VBA code module, before you write any subroutines …

Declare PtrSafe Sub mdlFile_findRelativePath Lib "stdmdlbltin.dll" ( _
    ByVal relativePath As String, _
    ByVal targetFileName As String, _
    ByVal rootFileName As String)

It's best if we wrap that function inside a VBA function, to hide some of the messy stuff. Here's VBA function FindRelativePath, which takes the root and target file paths as arguments, and returns the relative path from root to target …

Public Function FindRelativePath(ByVal rootFileName As String, ByVal targetFileName As String) As String
    Dim relative As String
    Const MAX_PATH = 260 ' Windows mandated value
    relative = Space(MAX_PATH)
    mdlFile_findRelativePath relative, targetFileName, rootFileName
    FindRelativePath = TrimToNull(relative)
End Function

Once of the messy technicalities it hides is the allocation of a string buffer for the MDL function. We do that in statement relative = Space(MAX_PATH), which ensures that the MDL function has somewhere to write its result.

C functions use NULL-terminated strings, unlike VB/VBA. We use the VBA TrimToNull function to remove any excess characters after the NULL …

Public Function TrimToNull(Text As String) As String
    Dim Pos As Integer
    Pos = InStr(1, Text, vbNullChar)
    If Pos > 0 Then
        TrimToNull = Left(Text, Pos - 1)
    Else
        TrimToNull = Text
    End If
End Function

Putting that all together, here's your code to find the relative path from root to target. Copy the code below into your VBA module. Make sure that the function declaration of mdlFile_findRelativePath is at the top of your file, before any VBA subroutines. Put your cursor in subroutine RelativePathTest, and press the GO button …

' ---------------------------------------------------------
Declare PtrSafe Sub mdlFile_findRelativePath Lib "stdmdlbltin.dll" ( _
    ByVal relativePath As String, _
    ByVal targetFileName As String, _
    ByVal rootFileName As String)
' ---------------------------------------------------------
' TrimToNull
' This function returns the portion of Text that is to the left of the vbNullChar
' character (same as Chr(0)). Typically, this function is used with strings
' populated by Windows API procedures. It is generally not used for
' native VB Strings.
' If vbNullChar is not found, the entire Text string is returned.
' ---------------------------------------------------------
Public Function TrimToNull(Text As String) As String
    Dim Pos As Integer
    Pos = InStr(1, Text, vbNullChar)
    If Pos > 0 Then
        TrimToNull = Left(Text, Pos - 1)
    Else
        TrimToNull = Text
    End If
End Function
' ---------------------------------------------------------
'   FindRelativePath
'   Wrapper function around mdlFile_findRelativePath
' ---------------------------------------------------------
Public Function FindRelativePath(ByVal rootFileName As String, ByVal targetFileName As String) As String
    Dim relative As String
    Const MAX_PATH = 260 ' Windows mandated value
    relative = Space(MAX_PATH)
    mdlFile_findRelativePath relative, targetFileName, rootFileName
    FindRelativePath = TrimToNull(relative)
End Function
' ---------------------------------------------------------
Sub RelativePathTest()
    Dim root As String
    Dim target As String
    Dim relative As String
    target = "d:\data\references\ref.dgn"
    root = "d:\data\root.dgn"
    Debug.Print "Root: " & root
    Debug.Print "Target: " & target
    relative = FindRelativePath(target, root)
    Debug.Print "Relative: " & relative
End Sub