Nick

I see what you mean about adding error handling. I tried to use this in
another project with files in various directories from the first to the
last. I get a subscript out of range error at the line.

TempStr = TempStr & PathParts(i) & "\"

Is this happening because it can't find the PathPart?

Cells(iFile, iPathColA).Value = GetPathParts(sFile, 3, 3)
Cells(iFile, iPathColB).Value = GetPathParts(sFile, 4, 5)
'Cells(iFile, iPathColC).Value = GetPathParts(sFile, 5, -1)
'Cells(iFile, iPathColD).Value = GetPathParts(sFile, 5, 0)

Column B works fine with GetPathPArts(sFile,4, 4), but when I change it to
4,5 it doesn't like it.
4, -1 works as well, but I would like to break the folders down further.

Thanks,
Dylan

"NickHK" wrote in message
...
As you seems to have many requirements, it would be better to make a
function to get the parts, instead of repeating split/combine code.
Something like this below. Note you should add error handling:

Public Function GetPathParts(FullPath As String, _
ByVal StartPart As Long, _
ByVal EndPart As Long) _
As String

Dim i As Long
Dim PathParts() As String
Dim TempStr As String

PathParts = Split(FullPath, "\")

If StartPart < 0 Then
StartPart = UBound(PathParts) + StartPart
ElseIf StartPart 0 Then
StartPart = StartPart - 1
Else
GetPathParts = "Invalid StartPart"
Exit Function
End If

If EndPart < 0 Then
EndPart = UBound(PathParts) + EndPart
ElseIf EndPart 0 Then
EndPart = EndPart - 1
Else
EndPart = UBound(PathParts)
End If

For i = StartPart To EndPart
TempStr = TempStr & PathParts(i) & "\"
Next
If InStr(1, PathParts(EndPart), ".") 0 Then TempStr = Left(TempStr,
Len(TempStr) - 1)

GetPathParts = TempStr

End Function

Note that a -ve StartPart/EndPart counts from the right, whilst zero goes to
the end. A little testing produced these results:

P:\GB123\Rail\Jobs\Feb06\Surveys\Trial surveys\Kyle of
Lochalsh\Station\Proforma\Part01\Station 01.pfm

Start End Returned
1 1 P:\
1 2 P:\GB123\
8 -1 Kyle of Lochalsh\Station\Proforma\Part01\
8 -2 Kyle of Lochalsh\Station\Proforma\
3 5 Rail\Jobs\Feb06\
3 0 Rail\Jobs\Feb06\Surveys\Trial surveys\Kyle of
Lochalsh\Station\Proforma\Part01\Station 01.pfm
1 0 P:\GB123\Rail\Jobs\Feb06\Surveys\Trial surveys\Kyle of
Lochalsh\Station\Proforma\Part01\Station 01.pfm
-3 -1 Station\Proforma\Part01\

NickHK

"dd" <dd.dd wrote in message ...
Nick

I wonder if you can help me with the data in Column C

With the following snippet of code, I want the ..iPathColC).Value to equal
the path string between PathParts(8) and the filename:

That is, for the path:
P:\GB123\Rail\Jobs\Feb06\Surveys\Trial surveys\Kyle of
Lochalsh\Station\Proforma\Part01\Station 01.pfm, I want to extract
\Proforma
and any other directories before the filename into the ...
iPAthColC).Value


sFile = oFile.Path

PathParts = Split(sFile, "\")

Cells(iFile, iPathColA).Value = PathParts(6) & "\"
Cells(iFile, iPathColB).Value = PathParts(7) & "\"

Temp = Mid(sFile, Len(PathParts(6)) + Len(PathParts(7)))
Cells(iFile, iPathColC).Value = Left(Temp, Len(Temp) -
Len(PathParts(UBound(PathParts))))

Cells(iFile, iFileCol) = oFile.Name

Regards
D Dawson