VBA 03 - Application Path - get to Parent Folder

Question

Application: Excel

Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1)

I need to go back at least 2 Folders from the Workbook Path.

I cannot use Paths like "C:/Folder1", because the Application will be moved multiple times.

What is the workbook is placed at "C:\" or "C:\Temp" ? – Siddharth Rout Nov 11 '13 at 11:03 — Siddharth Rout, Nov 11 '13 at 11:03

jacouh · Answer 1 · 2013-11-11T11:14:59.157

17

Like this:

Function getParentFolder2(ByVal strFolder0)
  Dim strFolder
  strFolder = Left(strFolder0, InStrRev(strFolder0, "\") - 1)
  getParentFolder2 = Left(strFolder, InStrRev(strFolder, "\") - 1)
End Function


Dim strFolder
strFolder = getParentFolder2(ThisWorkbook.Path)

We here cut twice \subdir pattern...

edited Nov 11 '13 at 11:14

answered Nov 11 '13 at 10:55

jacouh

8,473
5
32
43

score 6 · Accepted Answer · answered Aug 12 '18 at 20:56

6

The FileSystemObject provides the method GetParentFolderName(path).

See How do I use FileSystemObject in VBA?

Example

Dim fso As New FileSystemObject
Dim strParent As String

strParent = fso.GetParentFolderName(Me.Range("A1").value)

answered Aug 12 '18 at 20:56

Christopher Peisert

21,862
3
86
117

1

Also works in Access. Just remember to add a Reference to the `Tools / References / Microsoft Scripting Runtime` – Murrah Jun 26 '20 at 20:53

VBA 03 - Application Path - get to Parent Folder

2 Answers2

Example