vba string

[ericlch16]

I have something like that

string1 = C:\mydocuments\text\something\anything

i want a quick function to get one level up i.e

string2 =C:\mydocuments\text\something

how to get string2 from string1?

 

[hiker95]

ericlch16,

Try:

Option Explicit
Sub Test()
' hiker95, 04/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=543437
Dim string1 As String, string2 As String
Dim Sp, s As Long
string1 = "C:\mydocuments\text\something\anything"
Sp = Split(string1, "\")
For s = LBound(Sp) To UBound(Sp) - 1
  string2 = string2 & Sp(s) & "\"
Next s
If Right(string2, 1) = "\" Then string2 = Left(string2, Len(string2) - 1)
MsgBox string2
End Sub

 

[ericlch16]

any solution without the For loop?

ericlch16,

Try:

Option Explicit
Sub Test()
' hiker95, 04/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=543437
Dim string1 As String, string2 As String
Dim Sp, s As Long
string1 = "C:\mydocuments\text\something\anything"
Sp = Split(string1, "\")
For s = LBound(Sp) To UBound(Sp) - 1
  string2 = string2 & Sp(s) & "\"
Next s
If Right(string2, 1) = "\" Then string2 = Left(string2, Len(string2) - 1)
MsgBox string2
End Sub

 

[xenou]

Sub Foo()
    Debug.Print CreateObject("Scripting.FileSystemObject").GetParentFolderName("C:\SomeFolder\SomeFolder")
End Sub

Interestingly, it is not necessary for these folders to actually exist - it appears the function parses the file path in some manner no doubt very similar to Hiker's code.

 

[NateO]

Hello,

There's a few other ways to do this without looping, probably more, e.g.,

Sub foo()
Dim strArr() As String
Let strArr = Split("C:\Temp\Gooney goo goo", "\")
ReDim Preserve strArr(UBound(strArr) - 1)
MsgBox Join$(strArr, "\")
End Sub
 
Sub bar()
Const StringVar As String = "C:\Temp\Gooney goo goo"
MsgBox Left$(StringVar, Len(StringVar) - _
    WorksheetFunction.Find("\", StrReverse(StringVar)))
End Sub

But I don't fully follow why you'd be so sure to walk away from loops? They can be quite fast in well-constructed code. I guarantee the above is iterative, under-the-hood, eh.

 

[dk]

How about:

Function UpOneLevel(sFilepath As String) As String

UpOneLevel = Left$(sFilepath, InStrRev(sFilepath, "\", IIf(Right$(sFilepath, 1) = "\", Len(sFilepath) - 1, -1)))

End Function