Get Parent directory

[Guest]

The following function gives me my current directory. How can I adjust to get
the parent directory?

Function aPath()
aPath = ThisWorkbook.Path
End Function

Bruce

 

[Dave Peterson]

With no validation (in case you're at the root directory to start):

apath = thisworkbook.path & "\.."

Remember your old DOS commands:
CD ..
to back up one level

 

[Chip Pearson]

Bruce,

Here's a function that will return the parent folder name of a specified
folder or file. The specified folder or file need not exist. If
FolderNameOnly is omitted or False, the entire path to the parent is
returned. Otherwise, only name of the parent (with no path info) is
returned. UNC file names (e.g., "\\Dell8250\MainShare\Folder1\Folder2") are
supported.


Function ParentDirectory(FullPathName As String, _
Optional FolderNameOnly As Boolean = False) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ParentDirectory
' Returns the name of the parent of FullPathName. If FolderNameOnly
' is False or omitted, the fully qualified path name of the parent
' is returned. If FolderNameOnly is True, only the name (without
' path info) of the parent is returned. FullPathName may be either
' a relative or absolute path. FullPathName may be an UNC name
' (e.g., "\\Dell8250\MainShare\Folder\Folder2"). FullPathName need
' not exist.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos1 As Integer
Dim Pos2 As Integer

''''''''''''''''''''''''''''''''''''''''''
' exclude the trailing slash if it exists.
''''''''''''''''''''''''''''''''''''''''''
If StrComp(Right(FullPathName, 1), "\", vbBinaryCompare) = 0 Then
Pos1 = Len(FullPathName) - 1
Else
Pos1 = Len(FullPathName)
End If

'''''''''''''''''''''''''''''''''''''''
' Find the last '\' character
'''''''''''''''''''''''''''''''''''''''
Pos1 = InStrRev(FullPathName, "\", Pos1)
If Pos1 Then
'''''''''''''''''''''''''''''
' Found a '\' char.
'''''''''''''''''''''''''''''
If Pos1 > 1 Then
If StrComp(Mid(FullPathName, Pos1 - 1, 1), "\", vbBinaryCompare) = 0
Then
''''''''''''''''''''''''''''''''''''''''
' if the char to the left of Pos1 is
' also a '\' char, we have a server
' name (e.g., "\\Dell8250") not a
' path.
''''''''''''''''''''''''''''''''''''''''
ParentDirectory = vbNullString
Else
''''''''''''''''''''''''''''''''''''''''
' We don't have just server name (alone)
''''''''''''''''''''''''''''''''''''''''
Pos2 = InStrRev(FullPathName, "\", Pos1 - 1)
If Pos2 Then
''''''''''''''''''''''''''''''''''''
' Found a '\' to left of Pos1. Return
' either folder name or path name
''''''''''''''''''''''''''''''''''''
If FolderNameOnly = False Then
ParentDirectory = Left(FullPathName, Pos1 - 1)
Else
ParentDirectory = Mid(FullPathName, Pos2 + 1, Pos1 -
Pos2 - 1)
End If
Else
'''''''''''''''''''''''''''''''
' FullPathName has no parent.
'''''''''''''''''''''''''''''''
ParentDirectory = vbNullString
End If
End If
Else
'''''''''''''''''''''''''''''''''''
' no '\' char found. FullPathName
' has no parent.
'''''''''''''''''''''''''''''''''''
ParentDirectory = vbNullString
End If
Else
''''''''''''''''''''''''''''''
' no '\' found. Has no parent.
''''''''''''''''''''''''''''''
ParentDirectory = vbNullString
End If

End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)