Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA 03 - Application Path - get to Parent Folder

Tags:

excel

vba

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.

like image 594
Dennis Avatar asked Nov 11 '13 10:11

Dennis


People also ask

How do I browse files in VBA?

VBA Open File Dialog Box helps to browse a file and open Excel Workbook. Users can choose file by clicking on the button to open an Excel File with specific filter, title.


2 Answers

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...

like image 59
jacouh Avatar answered Oct 08 '22 23:10

jacouh


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)
like image 36
Christopher Peisert Avatar answered Oct 08 '22 22:10

Christopher Peisert