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