Parsing part of file path

[AChuckle]

I am trying to parse out part of a file path where my excel file is stored using a VBA function.

My file path looks something like this: \\ServerName\GroupA\GroupB\2011\08-01\Name\B.Folder\Summary.xls

The function needs to keep only this part of the file path: \\ServerName\GroupA\GroupB\2011\08-01\Name\

Not really sure where to start on this problem. Seen a few ways to parse the file name from a file path but not a different section.

My intent is to change the end of the original file path from where my Summary.xls file is saved up one folder, over, and then down two more. Basically I want to take the parsed path the funtcion returns and add two more file locations down.

Anyone have a possible solution?

Thanks!

 

[kpark91]

something like this?

Sub Test()
    Dim strTest, buffer As String
    Dim pos, prevPos, prevPos2 As Long
    
    prevPos = 0
    
    strTest = "\\ServerName\GroupA\GroupB\2011\08-01\Name\B.Folder\Summary.xls"
    
    For n = 1 To Len(strTest)
        pos = InStr(n, strTest, "\")
        If (pos > prevPos) Then
            prevPos2 = prevPos
            prevPos = pos
        End If
    Next n
    
    buffer = Left(strTest, prevPos2)
    
End Sub

 

[Scott Huish]

Function Parsename(r As String) As String
Dim fn As String, t, x As Long
fn = Mid(r, 2)
t = Split(fn, "\")
t(UBound(t) - 1) = "": t(UBound(t)) = ""
Parsename = "\" & Replace(Join(t, "\"), "\\", "\")
End Function

Sub test()
MsgBox Parsename("\\ServerName\GroupA\GroupB\2011\08-01\Name\B.Folder\Summary.xls")
End Sub

 

[AChuckle]

Thanks for the replies. Very helpful. Tried them both out but the first was the one I could get working. When calling up the function HOTPEPPER posted I kept getting an error saying "Argument not Optional". The function is called after a macro button is pressed and the function itself was saved in a module. Not sure what that error means.

 

[Scott Huish]

Mine is written as a function not a sub, so you need to pass the pathname to it.

Notice the sub beneath the function, which calls the UDF.

 

[AChuckle]

Thanks!