CODEWITHSUNDEEP
vba
Ayung
Ayung

Reputation: 3

Extracting folder name from file path

Currently, I am using this on excel vba to generate a text box for me to input the file path:

Dim FilePath As String
Cells.Select
Selection.ClearContents
FilePath = InputBox("Hi Production Controller! Where is your file path?")

I need to extract 14 Feb 2020 from this file path:

O:\Folder1\Folder2\Folder3\2020\02 Feb\14 Feb 2020

and insert it into cell C1. Can I get some help? I am a beginner at vba.

Upvotes: 0

Views: 3253

Answers (3)

Leo Ś
Leo Ś

Reputation: 11

Try This...

Function FolderName(ByVal FullPath) As String
FolderName = ""
For P = 1 To Len(FullPath)
S = Mid(FullPath, P, 1)
If S = "\" Then
   FolderName = ""
Else:
   FolderName = FolderName & S
End If
Next P
End Function

in Case you are on top it returns drive letter i.e "C:" Leo

Upvotes: 1

braX
braX

Reputation: 11755

Split it using the blackslash, and then take the last item in the array:

Sub test()
  Dim sText As String
  Dim vSplit As Variant

  sText = "O:\Folder1\Folder2\Folder3\2020\02 Feb\14 Feb 2020"

  ' make sure folder string does not end with a backslash
  If Right$(sText, 1) = "\" Then sText = Left$(sText, Len(sText) - 1)

  ' Split into an array based on the backslash
  vSplit = Split(sText, "\")

  ' set cell C1 equal to the last item in the split array
  Range("C1") = vSplit(UBound(vSplit))

End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

I need to extract 14 Feb 2020 from this file path:

O:\Folder1\Folder2\Folder3\2020\02 Feb\14 Feb 2020

Try this

Option Explicit

Sub Sample()
    Debug.Print GetFileFolderFromPath("O:\Folder1\Folder2\Folder3\2020\02 Feb\14 Feb 2020")
End Sub

Public Function GetFileFolderFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then _
    GetFileFolderFromPath = GetFileFolderFromPath(Left$(strPath, Len(strPath) - 1)) + _
    Right$(strPath, 1)
End Function

Upvotes: 2

Related Questions