Extract the path from a full file-name in VBA

Question

I m new in VBA and below is my code which is not working, can any one of u can help?

Dim nPath1() As String
nPath1() = Split(nPath, "\")       

'Declare path as integer
 Dim path As Integer
'Getting array length
 path = UBound(nPath1())
 Dim lastname As String
 'For Loop
 For i = 0 To path-1
     lastname += nPath1(i)+"\"
 Next i

The above code is not working; my path string is Root\zTrash - No longer needed\NOC\NOC and what I want is Root\zTrash - No longer needed\NOC.

Answer 1

If you are fan of long formulas, this is another option:

left(nPath,len(nPath)-len(split(nPath,"\")(ubound(split(nPath,"\")))))

• The idea is that you split by \
• Then you get the last value in the array (with ubound, but you split twice)
• Then you get the difference between it and the whole length
• Then you pass this difference to the left as a parameter

Answer 2

If you want to remove just the last item from your path, you can do it this way:

Left(nPath, InStrRev(nPath, "\") - 1)

• InStrRev finds the position of the last occurrence of \

• Left truncates the string until that position

• The -1 is because you want also to remove that last \

Answer 3

This

For i = 0 To path-1

gives you full nPath1 array. If you want to skip last element (and I'm not sure what you exactly want), you should use path-2

Answer 4

Or you can try:

Sub sTest1()
 Dim nPath1 As Variant, st As String
 st = "Root\zTrash - No longer needed\NOC\NOC"
 nPath1 = Split(st, "\")
 ReDim Preserve nPath1(UBound(nPath1) - 1)
 st = Join(nPath1, "\")
 Debug.Print st
End Sub

This is useful if you want to remove more than one item (not just the last one) by changing 1 to 2 or 3 for example:

Sub sTest2()
 Dim nPath1 As Variant, st As String, n As Long
 st = "Root\zTrash - No longer needed\NOC\NOC"

 For n = 1 To 3
    nPath1 = Split(st, "\")
    ReDim Preserve nPath1(UBound(nPath1) - n)
    Debug.Print Join(nPath1, "\")
 Next

Results:

Root\zTrash - No longer needed\NOC
Root\zTrash - No longer needed
Root