Problems Extracting String Subsets From Source String

[Ark68]

I found this code that helps me (once corrected) extract certain string subsets from a string (path).
Consider this path example, the components relate to the the variables of the code. "O:\Products\A\hostFolder\preTitle - ftitle.fileExtension" The code below is meant to extact:

hostFolder = {hostFolder}
pretitle = {preTitle}
ftitle = {ftitle}
fileExtension = {fileExtension}

Sub SearchDirectories(folderPath As String, searchPattern As String, ByRef outputRow As Long)
    Dim fileSystem As Object
    Dim folder As Object
    Dim subFolder As Object
    Dim file As Object
    Dim matchFound As Boolean
    Dim cntFile As Integer
    Dim folderName As String, hostFolder As String
    Dim preTitle As String
    Dim ftitle As String, ftitle2 As String
    Dim fileExtension As String
    Dim parts() As String, parts2 As Variant
    Dim startPos As Long
    Dim endPos As Long
    Dim ws_dump As Worksheet '######
    
    ' Create a FileSystemObject
    
    Set fileSystem = CreateObject("Scripting.FileSystemObject")
    Set folder = fileSystem.GetFolder(folderPath)
    Set ws_dump = ThisWorkbook.Worksheets("DUMP")
    
    ws_dump.Activate '######
    ws_dump.Range("O2").Activate '######
    
    ' Initialize matchFound to False for the current folder
    matchFound = False
    
    ' Search for files with the partial name in the current folder
    For Each file In folder.Files
        If InStr(1, file.Name, searchPattern, vbTextCompare) > 0 Then
            matchFound = True
            If matchFound Then          ' If a matching file is found, add the folder path to the worksheet
                ws_dump.Cells(outputRow, "S").Value = folderPath
                hostFolder = Split(Split(folderPath, "\")(UBound(Split(folderPath, "\")) - 1), "\")(0) 'host folder
                ws_dump.Cells(outputRow, "R").Value = hostFolder
                parts = Split(Split(folderPath, "\")(UBound(Split(folderPath, "\")))(0), " - ")
                ftitle = Split(parts(1), ".")(0)
                fileExtension = Split(Split(folderPath, ".")(UBound(Split(folderPath, "."))), ".")(1)
                ws_dump.Cells(outputRow, "P").Value = ftitle
                ws_dump.Cells(outputRow, "Q").Value = fileExtension
                ftitle2 = Mid(folderPath, InStrRev(folderPath, "\") + 1)
                parts2 = Split(ftitle2, " - ")
                If UBound(parts2) >= 0 Then
                    preTitle = Trim(parts2(0))
                Else
                    preTitle = ""
                End If
                ws_dump.Cells(outputRow, "Q").Value = preTitle
                outputRow = outputRow + 1
            End If
            cntFile = cntFile + 1
            'Exit For
        End If
    Next file
    
    ' Recursively search subfolders
    For Each subFolder In folder.SubFolders
        SearchDirectories subFolder.Path, searchPattern, outputRow
    Next subFolder
End Sub

Because I really don't have a full understanding how this works, let me ask for support for the following:
1) The line in blue is giving me unexpected results. Using the above as an example, hostFolder is returning "A" instead of "hostFolder".
2) The line is red is returning a 'Type mismatch' error.

 

[RoryA]

I don't see the point of having two Split functions there when they are both splitting on the same delimiter. The code is finding the second to last item after splitting the folder path using backslash as the delimiter. I suspect you are not passing a a path that ends in a backslash to the function.
I'm not really clear on what you expect the red line to be doing, but I suspect it should be:

parts = Split(Split(folderPath, "\")(UBound(Split(folderPath, "\"))), " - ")

 

[Ark68]

Thank you so much Rory. I admit I have no real idea how this code works, so I plead full ignorance.

I don't see the point of having two Split functions there when they are both splitting on the same delimiter.

Are you referring the the blue line? The Split(Split(folderpath ... and UBound(Split(folderPath... ? Google sourced code so I can't answer that

The code is finding the second to last item after splitting the folder path using backslash as the delimiter. I suspect you are not passing a a path that ends in a backslash to the function.

I'm assuming this is in reference to the blue line. You are correct ... the value for folderPath does not have a trailing backslash delimiter. A real-life value of folderPath returns "O:\Products\A\AntiSeize\Component - Userguide.docx" . hostFolder I expect it to be AntiSeize. Unsure where I need to correct this as clearly the blue line is somewhat messed up.

I'm not really clear on what you expect the red line to be doing, but I suspect it should be:

Not sure either, but with my limited understanding of VBA, I'm thinking perhaps parts is an array of the portions found in folderPath then the subset with a " - " delimeter for which the next line applies the first value of the array to ftile (eg. the "Component" of the title). I applied your suggestion, which rid the original error, but susequently resulted in "Subscript out of range." error with :

ftitle = Split(parts(1), ".")

 

[RoryA]

folderPath should be exactly that - a folder path, not including file name (the code loops through all the files in the specified folder). So your blue line should probably be:

hostFolder = Split(folderPath, "\")(UBound(Split(folderPath, "\")) - 1) 'host folder

The red part should actually not be looking at folderPath at all as far as I can see, but rather at file.name

 

[Ark68]

OK, thank you. This code likely isn't going to provide me the full results I'm seeking so perhaps best to scrap, research and rewrite.