Extract a folder name from file path

Question

I have a file path (which is a connection path for the worksheet) in the following format:

C:\ExcelFiles\Data\20140522\File1_20140522.csv

I want to extract 20140522.

I tried using responses of How to extract groups of numbers from a string in vba, but they don't seem to work in my case.

Which 20140522 do you wish to extract? the folder name or the file name? — LBPLC, Jun 04 '14 at 10:35
If you can think through exactly what it is you want, and define it in more detail, you'll probably get a better answer (and in the process may well solve the problem yourself). Do you want to extract the first 8-digit number? The name of the third folder in the path (i.e. everything between the 3rd and 4th backslashes)? The name of the last folder in the path? — aucuparia, Jun 04 '14 at 10:49
@AshokVardhan Will these numbers always be in the "Data" folder? — LBPLC, Jun 04 '14 at 10:52

score 0 · Answer 1 · answered Jun 04 '14 at 10:40

0

please find below

Filename = "C:\ExcelFiles\Data\20140522\File1_20140522.csv"
a = Replace(Mid(Filename, InStrRev(Filename, "_") + 1, Len(Filename)), ".csv", "")

answered Jun 04 '14 at 10:40

Karthick Gunasekaran

2,697
1
15
25

score 0 · Answer 2 · answered Mar 17 '17 at 03:54

Try the following. Folder is selected.

Sub Folder_S()
Dim sFolder As FileDialog
On Error Resume Next
Set sFolder = Application.FileDialog(msoFileDialogFolderPicker)
If sFolder.Show = -1 Then
    Folder_Select sFolder.SelectedItems(1), True
End If
End Sub
Sub Folder_Select(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
Dim FSO As Object
Dim SourceFolder As Object
Dim FileItem As Object
Dim strFile As String
Dim FileName As Variant
Dim pathParts() As String
Dim pathPart As String
Dim i As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(SourceFolderName)
pathParts = Split(SourceFolder.Path, Application.PathSeparator)
pathPart = SourceFolder.Path
For i = 0 To UBound(pathParts)
    If pathParts(i) = "20140522" Then
        pathPart = pathParts(i - 0)
        Exit For
    End If
Next i
Row = ActiveCell.Row
With CreateObject("Scripting.Dictionary")
    For Each FileItem In SourceFolder.Files
        strFile = FileItem.Name
        .Item(strFile) = Array(FileItem.Name)
    Next FileItem
    If .Count > 0 Then
        For Each FileName In .Items
            Cells(Row, 2).Formula = pathPart
        Next FileName
    End If
End With

End Sub

score 0 · Answer 3 · answered Oct 04 '19 at 07:57

I found your question by searching a solution how to get a folder path from a file that is inside this folder path. But your question doesn't match exactly what I need. For those who by your question title will find it for the same purpose as I found, below is my function:

Function getFolderPathFromFilePath(filePath As String) As String

    Dim lastPathSeparatorPosition As Long

    lastPathSeparatorPosition = InStrRev(filePath, Application.PathSeparator)

    getFolderPathFromFilePath = Left(filePath, lastPathSeparatorPosition - 1)

End Function

In some solutions for this purpose, I used FSO, but it takes resources, and I think it isn't worthy to create FSO object if you need it only for this simple function.

score 0 · Answer 4 · answered Nov 20 '19 at 18:05

the accepted answer is not accurate to read the folder name. here is more dynamic code. use splitter which splits string based on delimeter and makes an array. now read the second last element in array, thats the folder name.

Dim fileName As String

fileName = "C:\ExcelFiles\Data\20140522\File1_20140522.csv"

Dim vPathSplitter As Variant
vPathSplitter = Split(fileName, "\")
MsgBox (vPathSplitter(UBound(vPathSplitter) - 1))

score -1 · Accepted Answer · answered Jun 04 '14 at 10:45

-1

The below answer gets your file path from a range, rather than a fixed string. Much more handy if your planning on getting your filename from your sheets, which I imagine you are.

Sub GetFileDate()

Dim filename As String

filename = Sheets("Sheet1").Range("C9").Value 'Or Wherever your file path is

MsgBox Replace(Right(filename, 12), ".csv", "")


End Sub

This assumes the numbers your extracting will ALWAYS be dates in YYYYMMDD format and the file type is always .csv

answered Jun 04 '14 at 10:45

LBPLC

1,570
3
27
51

Thanks for the help. I used a code similar to the above one except that I extracted the numbers out of the file path and used Left(num,8) function to extract the first 8 digits which is the required number. – pikachuchameleon Jun 04 '14 at 10:57
@AshokVardhan No problem! Remember to give credit where credit is due :) – LBPLC Jun 04 '14 at 10:59

Extract a folder name from file path

5 Answers5