現在のExcelファイルを基準に相対パスで指定したファイルを開く

まずはプログラムの完成品

Function OpenRelativeFile(relativePath As String) As Boolean
    Dim currentFolderPath As String
    Dim targetFilePath As String

    ' 現在のファイルのフォルダパスを取得
    currentFolderPath = ThisWorkbook.Path

    ' 現在のディレクトリと相対パスを結合してフルパスを作成
    targetFilePath = currentFolderPath & "\" & relativePath

    ' ファイルの存在を確認し、開く
    If Dir(targetFilePath) <> "" Then
        Workbooks.Open targetFilePath
        MsgBox "ファイルが開かれました！", vbInformation, "成功"
        OpenRelativeFile = True
    Else
        MsgBox "指定したファイルが見つかりません。", vbExclamation, "エラー"
        OpenRelativeFile = False
    End If
End Function

Sub TestOpenFile()
    Call OpenRelativeFile("targetFile.xlsx")
End Sub

１行ずつ解説

1： Function を使って 戻り値のある関数 を定義。

Function OpenRelativeFile(relativePath As String) As Boolean

relativePath As String は 開きたいファイルの相対パスを受け取る引数。
As Boolean により、処理の成功 (True) / 失敗 (False) を呼び出し元に返す。こうすることで呼び出し元の関数の中で処理を分岐でき、プログラムの可読性を上げることができる。

2：relativePath を currentFolderPath に結合し、開くべきファイルのフルパスを作成。

targetFilePath = currentFolderPath & "\" & relativePath

例：
　　relativePath = "..\targetFile.xlsx" ⇒
　　　　"C:\Users\Username\Documents..\targetFile.xlsx"
　　Windowsが .. を解釈し、C:\Users\Username\targetFile.xlsx となる。

3：Dir(targetFilePath) で 指定したファイルが存在するかを確認し、存在すれば開く。

If Dir(targetFilePath) <> "" Then
    Workbooks.Open targetFilePath
    MsgBox "ファイルが開かれました！", vbInformation, "成功"
    OpenRelativeFile = True
Else
    MsgBox "指定したファイルが見つかりません。", vbExclamation, "エラー"
    OpenRelativeFile = False
End If

ファイルがある場合 → ファイル名 を返す（<> "" なので True）。
ファイルがない場合 → ""（空文字）を返す（<> "" は False になる）。
If 条件で、ファイルの有無によって処理を分岐。

4：Sub内で関数を実行してファイルを呼び出す。

Sub TestOpenFile()
    Call OpenRelativeFile("targetFile.xlsx")
End Sub

異なるディレクトリの場合は以下のように引数を入力する
　親ディレクトリにあるファイル　⇒　..\targetFile.xlsx
　親ディレクトリの中のサブフォルダにあるファイル　⇒　..\subfolder\targetFile.xlsx
　サブフォルダ内のファイル　⇒　subfolder\targetFile.xlsx

結論

以上のようにして別のディレクトリのファイルを相対パスとして扱うことができる。