OzGrid

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

< Back to Search results

 Category: [Excel]  Demo Available 

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.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to reference text in cell to unhide worksheet
How to reference a cell that contains a word to into a cell that has a sentence
How to change reference columns in another worksheet using VBA looping
How to use VBA code using relative references

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)