Find length of string - firstfolder

[dd]

I have a string which will contain strings of varying lengths which contain
the path to a file.
For example:
P:\firstfolder\secondfolder\thirdfolder\fourthfolder\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

 

[Guest]

root = "P:\firstfolder\secondfolder"
s =
"P:\firstfolder\secondfolder\thirdfolder\fourthfolder\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))

 

[Bob Phillips]

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

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfolder\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]

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
Dim sFile As String
Dim iPos As Long
Dim ipos2 As Long

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfolder\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)

 

[Guest]

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

 

[dd]

Thanks Bob,

I managed to get it to work.

Thanks for your input as well Tom,

sincerely
Dylan

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

sFile =
"P:\firstfolder\secondfolder\thirdfolder\fourthfolder\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)