CODEWITHSUNDEEP
vbaexcel
user2977985
user2977985

Reputation:

Extract the path from a full file-name in VBA

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.

Upvotes: 10

Views: 17023

Answers (5)

T.M.
T.M.

Reputation: 9948

Approach using Filter() and one Split() action

In addition to @Vityata 's answer I demonstrate an array alternative accepting also vbNullString as FullPath argument.

Based on the same idea to make disappear the last split token, this approach doesn't calculate item lengths, but removes the last item directly via Filter(a, a(Ubound(a)), False).

Function getPath(FullPath As String, Optional Delim As String = "\") As String
    Dim a: a = Split(FullPath & "$", Delim)
    getPath = Join(Filter(a, a(UBound(a)), False), Delim)
End Function

Side note to Split()

The addition of & "$" to FullPath argument is necessary to make the last split item unique, otherwise it would remove all NOC tokens, not only the last item. So an Example call like Debug.Print getPath("Root\zTrash - No longer needed\NOC\NOC") returns the wanted result Root\zTrash - No longer needed\NOC.

If an empty string would be splitted, there won't occur an error as the zero array boundaries (i.e. 1 item) "join" to another vbNullString.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

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

Upvotes: 2

Fadi
Fadi

Reputation: 3322

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

Upvotes: 2

A.S.H
A.S.H

Reputation: 29332

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 \

Upvotes: 23

dmitry
dmitry

Reputation: 468

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

Upvotes: -1

Related Questions