root = "P:\firstfolder\secondfolder"
s =
"P:\firstfolder\secondfolder\thirdfolder\fourthfol der\fifthfolder\filename.xls"
v = Split(s,"\")
rootFolderCnt = len(root) - len(replace(s,"\","")
sPath = "\"
for i = rootFolderCnt + 2 to ubound(v) - 1
sPath = sPath & v(i)
Next
cells(1,1) = v(rootFolderCnt + 1)
cells(1,2) = sPath
cells(1,3) = v(ubound(v))

--
Regards,
Tom Ogilvy



"dd" wrote:

I have a string which will contain strings of varying lengths which contain
the path to a file.
For example:
P:\firstfolder\secondfolder\thirdfolder\fourthfold er\fifthfolder\filename
The root of the path for this example is P:\firstfolder\secondfolder\
In reality the folder names are all varying lengths.

The code loops through all folders in a specific root to find a filetype.
It presently puts the path after the root in one column and the filename in
the next.
The filename column contains hyperlinks by adding the root, path and file
together.

I want the path to be split so that the third folder is put in Column A, the
remaining path is put in the Column B and the filename in Column C. I want
to alter the existing code to do this.

I want to use Mid(String,Start,Length) to find the Length of the string for
the first folder in the path after the root (thirdfolder) and before fourth
folder.

Does anyone know the syntax I should use?
Or, is there an easier alternative?

DDAwson
Scotland