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

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

Dim sFile As String
Dim iPos As Long
Dim ipos2 As Long

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfol der\fifthfolder\filename"

iPos = InStr(InStr(InStr(sFile, "\") + 1, sFile, "\") + 1, sFile, "\")
Range("A1").Value = Left(sFile, iPos)
ipos2 = InStrRev(sFile, "\")
Range("A2").Value = Mid(sFile, iPos + 1, ipos2 - iPos)
Range("A3").Value = Right(sFile, Len(sFile) - ipos2)


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dd" <dd.dd wrote in message
...
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

Bob

Could you have a glance over this code and advise if it can be amended with
your previous advice?

Thanks
Dylan

'---------------------------------------------------------------------------
Sub selectFiles(ByVal sPath As String, _
ByVal filetype As String, _
ByVal subfolders As Boolean)
'---------------------------------------------------------------------------
Dim oFolder As Object
Dim oFldr As Object
Dim oFiles As Object
Dim oFile As Object

'This uses FSO (FileSystemObject) which is an MS scripting facility to
'access many aspects of the file system.
'It uses late-binding, so no need to set a reference.
'It uses a recursive function (selectfiles) that is entered with the
'start folder, and if it finds any folders within, re-enters itself with
'the new folder as the argument.

Set oFolder = objFSO.GetFolder(sPath)

If oFolder.Files.Count 0 Then
For Each oFile In oFolder.Files
If oFile.Type = filetype Then

'Mid(string, start[, length])
'THIS IS THE CODE I AM ATTEMPTING TO AMEND
Cells(iFile, iPathColA).Value = Mid(oFile.Path, Len(sRoot) +
1, FindForward(Len(sRoot) + 1, "\")
Cells(iFile, iPathColB).Value = Mid(oFile.Path, Len(sRoot) +
1, FindForward(oFile.Path, "\") + 1 - (Len(sRoot) + 1))
Cells(iFile, iFileCol) = oFile.Name
' And a link in the Hyperlink column
Cells(iFile, iLinkCol).FormulaR1C1 = "=HYPERLINK(root &
RC[-2] & RC[-1] ,""HERE"")"
iFile = iFile + 1
End If
Next oFile
End If

'This puts Subfolders' Paths in the specified column of the worksheet
If subfolders Then
For Each oFldr In oFolder.subfolders
selectFiles oFldr.Path, filetype, True
Next oFldr
End If

End Sub
"Bob Phillips" wrote in message
...
Dim sFile As String
Dim iPos As Long
Dim ipos2 As Long

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfol der\fifthfolder\filename"

iPos = InStr(InStr(InStr(sFile, "\") + 1, sFile, "\") + 1, sFile, "\")
Range("A1").Value = Left(sFile, iPos)
ipos2 = InStrRev(sFile, "\")
Range("A2").Value = Mid(sFile, iPos + 1, ipos2 - iPos)
Range("A3").Value = Right(sFile, Len(sFile) - ipos2)


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dd" <dd.dd wrote in message
...
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

couple of typos. This works:

Sub EFG()
Root = "P:\firstfolder\secondfolder"
s = "P:\firstfolder\secondfolder\thirdfolder" & _
"\fourthfolder\fifthfolder\filename.xls"
v = Split(s, "\")
RootFolderCnt = Len(Root) - Len(Replace(Root, "\", ""))
sPath = "\"
For i = RootFolderCnt + 2 To UBound(v) - 1
sPath = sPath & v(i)
Next
Cells(5, 1) = v(RootFolderCnt + 1)
Cells(5, 2) = sPath
Cells(5, 3) = v(UBound(v))

End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:


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

Thanks Bob,

I managed to get it to work.

Thanks for your input as well Tom,

sincerely
Dylan

"Bob Phillips" wrote in message
...
Dim sFile As String
Dim iPos As Long
Dim ipos2 As Long

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfol der\fifthfolder\filename"

iPos = InStr(InStr(InStr(sFile, "\") + 1, sFile, "\") + 1, sFile, "\")
Range("A1").Value = Left(sFile, iPos)
ipos2 = InStrRev(sFile, "\")
Range("A2").Value = Mid(sFile, iPos + 1, ipos2 - iPos)
Range("A3").Value = Right(sFile, Len(sFile) - ipos2)


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"dd" <dd.dd wrote in message
...
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