Relative paths VBA

Question

I need to go from absolute to relative paths.

My macro is in folder \models\vba

and this macro uses sheets from the folders \models\Main ,and \models\aear as below

 Set Main = Workbooks.Open("D:\Shared\Models\Main\Main 2017.xlsx")

I've been shopping around already, and it seems its easiest to use

ThisWorkbook.Path

What's the best way to go back one directory, and then enter the Macro map.?

Answer 1

You can construct relative paths the usual way but you need always to append them to ThisWorkbook.Path.

Set Main = Workbooks.Open(ThisWorkbook.Path & "\..\Main\Main 2017.xlsx")

Each .. goes one step up in the folder tree.

You can eventualy encapsulate this by building your own function that constructs the absolute path from your relative path

Function RelToAbs(RelPath as string) as string
    RelToAbs = ThisWorkbook.Path & "\" & RelPath
End function

Set Main = Workbooks.Open(RelToAbs("..\Main\Main 2017.xlsx"))