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

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\subsubfo lder\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



"Paul Martin" wrote:

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

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)


"Patrick Molloy" wrote in message
...
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\subsubfo lder\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



"Paul Martin" wrote:

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

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

Thanks to both of you.

Paul Martin
Melbourne, Australia