Go up one folder level

Question

I have a Macro that gets sub folder data. However I also want something from the main folder.

I looked at How to get current working directory using vba? but need to change activeworkbook path:

Application.ActiveWorkbook.Path might be "c:\parent\subfolder"

I would want

"c:\parent\"

Using Excel 365 VBA

Alex K. · Accepted Answer · 2016-09-22T11:10:28.767

12

As the path may not be the current working directory you need to extract the path from the string.

Find the last \ and read all characters to the left:

ParentPath = Left$(Path, InStrRev(Path, "\"))

If you are working around the current directory ChDir ".." will jump you up one level, the new path can be returned by CurrDir.

edited Sep 22 '16 at 11:10

answered Sep 22 '16 at 11:05

Alex K.

171,639
30
264
288

I should say it will always be in the working directory (in this case!). But good to know! – indofraiser Sep 22 '16 at 11:07
Try it like this: Left$(application.ActiveWorkbook.Path, InStrRev(application.ActiveWorkbook.Path, "\")-1) – Vityata Sep 22 '16 at 11:09
Thanks. Spotted I'd put the code a line to early as well! (On the main code) – indofraiser Sep 22 '16 at 11:15

score 7 · Answer 2 · answered Sep 22 '16 at 12:52

7

The most reliable way to do this is to use the Scripting.FileSystemObject. It has a method that will get the parent folder without trying to parse it:

With CreateObject("Scripting.FileSystemObject")
    Debug.Print .GetParentFolderName(Application.ActiveWorkbook.Path)
End With

answered Sep 22 '16 at 12:52

Comintern

21,855
5
33
80

This gives you the `subfolder`, not the `parent` folder. – TJJ Aug 02 '22 at 12:47

score 0 · Answer 3 · answered Oct 31 '20 at 00:05

Dim WbDir As String
Dim OneLvlUpDir As String

'get current WorkBook directory
WbDir = Application.ActiveWorkbook.Path

'get directory one level up
ChDir WbDir
ChDir ".."

'print new working directory and save as string. Use as needed.
Debug.Print CurDir()
OneLvlUpDir = CurDir()

score -1 · Answer 4 · edited Sep 04 '20 at 05:04

-1

I think you mean this solution:

Sub t()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
MsgBox "ThisWorkbook.Path = " & ThisWorkbook.Path & vbLf & _
"Path one folder down = " & fso.GetFolder(ThisWorkbook.Path & "\." & "NewFolder").Path
Set fso = Nothing
End Sub

edited Sep 04 '20 at 05:04

David Buck

3,752
35
31
35

answered Sep 04 '20 at 03:30

Abu Dujana

1

Go up one folder level

4 Answers4

Linked