Given the following path:

s = Excel.ActiveWorkbook.Path

how can I find the parent folder 3 levels up?


Eg if s = p:\paydata\hrimp\library\monthlystats\2005\12\Chie fExec

i want to be able to return p:\paydata\hrimp\library\monthlystats

I could do it by searching for the \ characters from the end of the
string, but it is there any easier way to do this?

Nirmal Singh

An easy way would be to use the FileSystemObject in the Scripting
library. In VBA, go to the Tools menu, choose References, and put
a check next to "Microsoft Scripting Runtime". Then, use code
like

Dim FSO As Scripting.FileSystemObject
Dim Fldr As Scripting.Folder
Set FSO = New Scripting.FileSystemObject
Set Fldr = FSO.GetFolder(ActiveWorkbook.Path)
Set Fldr = Fldr.ParentFolder.ParentFolder
Debug.Print Fldr.Path


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Nirmal Singh" wrote in
message ...

Given the following path:

s = Excel.ActiveWorkbook.Path

how can I find the parent folder 3 levels up?


Eg if s =
p:\paydata\hrimp\library\monthlystats\2005\12\Chie fExec

i want to be able to return
p:\paydata\hrimp\library\monthlystats

I could do it by searching for the \ characters from the end of
the
string, but it is there any easier way to do this?

Nirmal Singh

Try using GetParentFolderName.

As in the following function:

Function GetParentFoder3Up(ByVal s As String) As String
Dim fs As Variant
Set fs = CreateObject("Scripting.FileSystemObject")

s = fs.GetParentfoldername(s)
s = fs.GetParentfoldername(s)
GetParentFoder3Up = fs.GetParentfoldername(s)
Set fs = Nothing

End Function

HTHs.

"Nirmal Singh" wrote:


Given the following path:

s = Excel.ActiveWorkbook.Path

how can I find the parent folder 3 levels up?


Eg if s = p:\paydata\hrimp\library\monthlystats\2005\12\Chie fExec

i want to be able to return p:\paydata\hrimp\library\monthlystats

I could do it by searching for the \ characters from the end of the
string, but it is there any easier way to do this?

Nirmal Singh

On Wed, 14 Dec 2005 13:16:23 -0600, "Chip Pearson"
wrote:

An easy way would be to use the FileSystemObject in the Scripting
library. In VBA, go to the Tools menu, choose References, and put
a check next to "Microsoft Scripting Runtime". Then, use code
like

Thanks Chip, that is exactly what I needed.

Nirmal Singh