Dear All master,
I want to get the path of the parent folder as in column F and the path of the subfolder as in column G which I marked in yellow. I want to just add a little in the code below and don't want to change the structure of the code below
thanks
roykana
I want to get the path of the parent folder as in column F and the path of the subfolder as in column G which I marked in yellow. I want to just add a little in the code below and don't want to change the structure of the code below
Book3 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | PATH | FILENAME | KODE | ITEM | V | PARENTFOLDERPATH | SUBFOLDERPATH | ||
2 | \\server-pc\catalog\catalog\ARTIKEL ACAK TAMIKO\111138(1).jpg | 111138(1).jpg | 111138 | \\server-pc\catalog\catalog | \ARTIKEL ACAK TAMIKO\ | ||||
3 | \\server-pc\catalog\catalog\111139(1).jpg | 111139(1).jpg | 111139 | \\server-pc\catalog\catalog | |||||
4 | \\server-pc\catalog\catalog\catalog_H-Ride\H98601.jpg | H98601.jpg | H98601 | \\server-pc\catalog\catalog | \catalog_H-Ride\ | ||||
5 | \\server-pc\catalog\catalog\catalog_KaryaAsiaJaya\K102016.jpg | K102016.jpg | K102016 | \\server-pc\catalog\catalog | \catalog_KaryaAsiaJaya\ | ||||
6 | \\server-pc\catalog\catalog\KOSWARA-NO BRAND\04000.jpg | 04000.jpg | 04000 | \\server-pc\catalog\catalog | \KOSWARA-NO BRAND\ | ||||
7 | \\server-pc\catalog\catalog\OTHERS\0119.jpg | 0119.jpg | 0119 | \\server-pc\catalog\catalog | \OTHERS\ | ||||
8 | \\server-pc\catalog\catalog\OTHERS\new\1023.. ..jpg | 1023.. ..jpg | 1023.. . | \\server-pc\catalog\catalog | \OTHERS\new\ | ||||
9 | \\server-pc\catalog\catalog\OTHERS\new\R67.jpg | R67.jpg | R67 | \\server-pc\catalog\catalog | \OTHERS\new\ | ||||
Master |
thanks
roykana
VBA Code:
Sub GetFileName2()
Dim lr As Long
Dim rng As Range
Dim arr1() As String
Dim arr2() As String
Dim arr3() As String
Application.ScreenUpdating = False
' Find last row in column A with data
Sheets("Master").Select
lr = Cells(Rows.Count, "A").End(xlUp).Row
' Pre-format column C for text
Columns("C:C").NumberFormat = "@"
' Loop through every cell in column A starting in row 2
For Each rng In Range("A2:A" & lr)
arr1 = Split(rng.Value, "\")
rng.Offset(0, 1).Value = arr1(UBound(arr1, 1))
arr2 = Split(arr1(UBound(arr1, 1)), "(")
arr3 = Split(arr2(0), "-")
' If first member of array is blank, choose the second
If Left(arr2(0), 1) = "-" Then
rng.Offset(0, 2).Value = Replace(arr2(0), ".jpg", "")
Else
rng.Offset(0, 2).Value = Replace(arr3(0), ".jpg", "")
End If
Next rng
Application.ScreenUpdating = True
End Sub