We help IT Professionals succeed at work.

Got folder name from path, before filename (VBA)

jasocke2
jasocke2 asked
on
Hi,

This should be quite a simple one.

If I have a variable (mypath) which is set to C:/Folder1/folder2/file.xls in Excel VBA, what I want to do is extract the folder name before the file.xls which would be 'folder2'.

What VBA function could I use to get this and assign it to the variable 'MyFolder'?

Thanks,
Comment
Watch Question

Top Expert 2010

Commented:
Hello jasocke2,

Split the path into an array, and take the next to last member of the array:

MsgBox Split(mypath, "\")(UBound(Split(mypath, "\") - 1)

Regards,

Patrick
Commented:
Hi

You could use a function like the attached.

Thanks
Jason
Sub Main()
Dim FullPath, MyFolder
FullPath = "C:/Folder1/folder2/file.xls"
MyFolder = GetFolder(FullPath)
MsgBox MyFolder
End Sub

Function GetFolder(FullPath)
Lastslashpos = InStrRev(FullPath, "/")
TruncatedPath = Left(FullPath, Lastslashpos - 1)
Lastslashpos = InStrRev(TruncatedPath, "/")
GetFolder = Right(TruncatedPath, Len(TruncatedPath) - Lastslashpos)
End Function

Open in new window

Top Expert 2010
Commented:
Sorry, should be:

MsgBox Split(mypath, "\")(UBound(Split(mypath, "\")) - 1)

Normally a path would have backslashes, not forward slashes...

Patrick

Author

Commented:
Spot on thanks :)