Using excel VBA open file based on cell value with path composed on the fly from same value

This is the goal. User open’s read only workbook from network drive. Selects a cell with the file name and clicks on macro button which will find and open how every many files there are that match the cell value.

What’s the most efficient method of achieving that result? Keeping in mind that:

• User’s don’t want to wait for excel to compile a directory structure with files every time they open the workbook.
• file names will always match some portion of the containing folder name
  • file 135A1200 would be in folder 135A12XX
• dozens of folders/subfolders with varying levels of subfolders
• thousands of files with constant changes
• folder hierarchy is semi-permanent
• consistent file/folder naming format in that:
  • will always start with three numbers
  • followed by one to three letters
  • then four to seven numbers e.g., 135A1200 or 246FP317101
• didn’t want to use collections, etc. because of so many unknowns

I was thinking of having a loop of sorts that will take the value, build a path from that value while also verifying that the path exists before moving on, and then when the final sub-folder is reached, locate however many files that match the value and do whatever. I’m having trouble with looping and adding the X’s in the sub-folders because they don’t match the selected value and it’s not always known where the X’s will be in a different sub-folder set.

135A1200-101 would equal path135135A135A1XXX135A12XX135A1200_S_01.file

Or

246FP317101-31 would equal path246246F246FP246FP317101.file

• \path135
  • 135A
    • 135A0XXX
    • 135A1XXX
    • 135A10XX
    • 135A11XX
    • 135A12XX
      • 135A1200_S_01.file
      • 135A1200_S_02.file
      • 135A1200_S_03.file
    • 135A13XX
    • 135A3XXX
    • 135ASKXXX
  • 135D
  • 135F
  • 135GGG
  • 135LL
• \path321
• \path246
  • 246F
    • 246F13
    • 246F14
    • 246F15
    • 246F16
    • 246FF
    • 246FP
      • 246FP317101.file

This is what I have which works ok for a simpler set of files and folders.

Public Sub pickFiles()
    Dim File As Variant
    Dim subPath As String

    File = Selection(1, 1).Value

    Select Case Left(File, 1)
    Case "Q"
        If Left(File, 6) = "Q11-12" Then
            subPath = "folderQXXQ11" & Left(File, 6)
        ElseIf Left(File, 6) = "Q11-14" Then
            subPath = "folderQXXQ11" & Left(File, 6)
        ElseIf Left(File, 6) = "Q11-22" Then
             subPath = "folderQXXQ11" & Left(File, 6)
        Else
            subPath = "folderQXX" & Left(File, 3)
        End If
            openCompFile File, subPath
    Case "P"
        subPath = "folderPXX" & Left(File, 3)
        openCompFile File, subPath
    Case Else
        msgbox "That's not a valid file number", vbInformation
    End Select
End Sub

Private Sub openCompFile(ByRef File As Variant, ByRef subPath As String)
    Dim mainPath As String
    Dim fso As New FileSystemObject
    Dim Folder As Folder
    'Dim File As Variant
    Dim FileCollection As New Collection

    mainPath = "X:folder" & subPath

    Set Folder = fso.GetFolder(mainPath)

    For Each File In Folder.Files
        If Left(File.Name, 9) = Left(File, 9) Then FileCollection.Add File
    Next File

    If FileCollection.Count = 0 Then
        msgbox Left(File, 9) & " was not found.", vbInformation
    Else
        For Each File In FileCollection
            ShellExecute 0, "Open", File.Path, vbNullString, vbNullString, 1
        Next
    End If
End Sub