Extract path from full file name in VBA

Question

I m new in VBA and below my code that does not work, can any of u 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 does not work; my path line is Root \ zTrash - no longer needed \ NOC \ NOC , and I want Root \ zTrash - no longer needed \ NOC .

Answer 1

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

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

• InStrRev finds the position of the last occurrence \

• Left trims the string to this position

• -1 is that you also want to delete the last \

Answer 2

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 delete more than one element (not only the last) 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 

Answer 3

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

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

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

Answer 4

it

 For i = 0 To path-1 

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