How to reference a directory that is up one level from the ActiveWorkbook.Path

Requirement:

Is there any way to reference a directory that is up one level from the ActiveWorkbook.Path?

The user trying to accomplish three goals:

1. Maintain a Master Database in a root directory and pull data from a subdirectory:

Code:

FilePath = ActiveWorkbook.Path & "\sub-DirectoryName\"

2. Maintain a Minor Database located in a sub-Directory, Named by Month, and pull data from the Master Database located in the Root Directory (up one level).

3. Maintain a Minor Database in a sub-Directory, Named by Month, and pull data from a different Minor Database in a Different sub-Directory (e.g. up one level to root directory, then down one level to "January").

The Databases are going to be moved around A LOT, so the user has to use relative paths.

Solution:

Code:

Left(ActiveWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\"))

The VBA left command is very similar to the left function in excel =Left(text, numberofcharacters). The instrrev searches a string in reverse for the character or string you are looking for and returns a number reflecting its position in that string (the instr command searches from the start of the text similar to search and find functions in excel).

Combined you get all of the characters in the left of the path name up to and including the last "\", so instrrev(c:\path\subdir, "\") returns the number 8. Left(c:\path\subdir, 8) returns c:\path\.

Obtained from the OzGrid Help Forum.

Solution provided by Justin Doward.