title: Using excel VBA open file based on cell value with path composed on the fly from same value
body: <p>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.  </p>
  
  <p>What’s the most efficient method of achieving that result?  Keeping in mind that:</p>
  
  <ul>
  <li>User’s don’t want to wait for excel to compile a directory structure with files every time they open the workbook.</li>
  <li>file names will always match some portion of the containing folder name
  
  <ul>
  <li>file 135A1200 would be in folder 135A12XX</li>
  </ul></li>
  <li>dozens of folders/subfolders with varying levels of subfolders</li>
  <li>thousands of files with constant changes</li>
  <li>folder hierarchy is semi-permanent</li>
  <li>consistent file/folder naming format in that:
  
  <ul>
  <li>will always start with three numbers</li>
  <li>followed by one to three letters</li>
  <li>then four to seven numbers e.g., 135A1200 or 246FP317101</li>
  </ul></li>
  <li>didn’t want to use collections, etc. because of so many unknowns</li>
  </ul>
  
  <p>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.</p>
  
  <p>135A1200-101 would equal \path\135\135A\135A1XXX\135A12XX\135A1200_S_01.file</p>
  
  <p>Or</p>
  
  <p>246FP317101-31 would equal \path\246\246F\246FP\246FP317101.file</p>
  
  <ul>
  <li>\\path\135\
  
  <ul>
  <li>135A\
  
  <ul>
  <li>135A0XXX\</li>
  <li>135A1XXX\</li>
  <li>135A10XX\</li>
  <li>135A11XX\</li>
  <li>135A12XX\
  
  <ul>
  <li>135A1200_S_01.file</li>
  <li>135A1200_S_02.file</li>
  <li>135A1200_S_03.file</li>
  </ul></li>
  <li>135A13XX\</li>
  <li>135A3XXX\</li>
  <li>135ASKXXX\</li>
  </ul></li>
  <li>135D\</li>
  <li>135F\</li>
  <li>135GGG\</li>
  <li>135LL\</li>
  </ul></li>
  <li>\\path\321\</li>
  <li>\\path\246\
  
  <ul>
  <li>246F\
  
  <ul>
  <li>246F13\</li>
  <li>246F14\</li>
  <li>246F15\</li>
  <li>246F16\</li>
  <li>246FF\</li>
  <li>246FP\
  
  <ul>
  <li>246FP317101.file</li>
  </ul></li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>This is what I have which works ok for a simpler set of files and folders.</p>
  
  <pre><code>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 = "folder\QXX\Q11\" &amp; Left(File, 6)
          ElseIf Left(File, 6) = "Q11-14" Then
              subPath = "folder\QXX\Q11\" &amp; Left(File, 6)
          ElseIf Left(File, 6) = "Q11-22" Then
               subPath = "folder\QXX\Q11\" &amp; Left(File, 6)
          Else
              subPath = "folder\QXX\" &amp; Left(File, 3)
          End If
              openCompFile File, subPath
      Case "P"
          subPath = "folder\PXX\" &amp; 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\" &amp; 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) &amp; " was not found.", vbInformation
      Else
          For Each File In FileCollection
              ShellExecute 0, "Open", File.Path, vbNullString, vbNullString, 1
          Next
      End If
  End Sub
  </code></pre>