Excel VBA : How to break the file Path

[justuptou]

I have to choose the file and record the completely files name in excel cells. Have any coding to separate the "File name, Directory and sub-directory " and save it on worksheet ?

such as :
C:\Folder1\Folder2\Folder3\FileName1.jpg
C:\Folder1\Folder2\Folder4\FileName2.jpg
C:\Folder1\Folder2\Folder5\FileName3.jpg



Target : (Dim A(),B(),C() as String)

A = "C:\Folder1\Folder2"
B = "Folder3", "Folder4" or "Folder5"
C = "FileName1", "FileName2" or "FileName3"

[Paul_Hossler]

This could use some error checking

Option Explicit
Sub test()
    Dim sPath As String, F1 As String, F2 As String, F As String
    Dim i As Long
    
    i = 1
    With ActiveSheet
        sPath = "C:\Folder1\Folder2\Folder3\FileName1.jpg"
        Call SplitPath(sPath, F1, F2, F)
        .Cells(i, 1) = sPath
        .Cells(i, 2) = F1
        .Cells(i, 3) = F2
        .Cells(i, 4) = F
        
        i = i + 1
 
        sPath = "C:\Folder1\Folder2\Folder4\FileName2.jpg"
        Call SplitPath(sPath, F1, F2, F)
        Call SplitPath(sPath, F1, F2, F)
        .Cells(i, 1) = sPath
        .Cells(i, 2) = F1
        .Cells(i, 3) = F2
        .Cells(i, 4) = F
        
        i = i + 1
        sPath = "C:\Folder1\Folder2\Folder5\FileName3.jpg"
        Call SplitPath(sPath, F1, F2, F)
        Call SplitPath(sPath, F1, F2, F)
        .Cells(i, 1) = sPath
        .Cells(i, 2) = F1
        .Cells(i, 3) = F2
        .Cells(i, 4) = F
        
        i = i + 1
    End With
End Sub
Sub SplitPath(P As String, Folder1 As String, Folder2 As String, FileName As String)
    Dim v As Variant
    
    v = Split(P, "\")
    
    FileName = v(UBound(v))
    Folder2 = v(UBound(v) - 1)
    
    ReDim Preserve v(LBound(v) To UBound(v) - 1)
    Folder1 = Join(v, "\")
End Sub

[jolivanes]

Or did you mean something like this?

Sub Or_Do_You_Mean_This()
Dim c As Range, jve, j As Long
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
jve = Split(c, "\")
    For j = LBound(jve) To UBound(jve)
        c.Offset(, j + 1) = jve(j)
    Next j
Next c
End Sub

[justuptou]

Both is ok, thank you