Ascertaining the directory name (2 levels up)

[Paul Martin]

Hi all

I am using John Walkenbach's code to find the file name only from a
full path string. My structure is as follows:

PARENT_DIR
|
SUB_DIRECTORY
|
ThisWorkbook

I wish to ascertain the parent directory name. I have working code
(below) but am wondering if there's a simpler way to achieve this:
Dim sInitPath As String
Dim sNewPath As String
Dim sParentDir as string


sInitPath = CurDir
sNewPath = ThisWorkbook.Path & "\..\"

ChDrive sNewPath
ChDir sNewPath

sParentDir = FileNameOnly(CurDir)

ChDrive sInitPath
ChDir sInitPath

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Patrick Molloy]

We'll use the File System Object to navigate.
To acces this library, you need to set a reference in the IDE under
TOOLS/References to the Microsoft Scripting Runtime DLL.

Then, in a standard module, add this code:

Option Explicit
Sub test()
MsgBox GetPath(ThisWorkbook.FullName, 2)
End Sub


Function GetPath(sFull As String, lLevels As Long) As String
Dim fso As Scripting.FileSystemObject
Dim pos As Long
Set fso = New FileSystemObject

If fso.FileExists(sFull) Then
GetPath = fso.GetParentFolderName(sFull)
For lLevels = 1 To lLevels
pos = InStrRev(GetPath, "\")
If pos > 0 Then
GetPath = Left(GetPath, pos - 1)
End If
Next
Else
GetPath = ""
End If

End Function


you should eb able to see how it works. Basically a fullname will be
something like this

\\server\servername\mainfolder\subforder1\subsubfolder\filename.xls

using the iNSTREV() function gets us the psoition of the last '\' .... so
strippng this off gives the next directory 'up' the path, and so on

 

[Bob Phillips]

You could also use this way

Dim aryDirs

aryDirs = Split(ThisWorkbook.FullName, "\")
Debug.Print aryDirs(UBound(aryDirs) - 1)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Paul Martin]

Both those methods worked fine, though Bob's was easier and adequate
for me.

Thanks to both of you.

Paul Martin
Melbourne, Australia