Get VBA Code in excel to find a file and open it

Question

I am making a little report system that needs to get the name of a file from an excel cell and look for that in 12 possible folders. all the folders are labeled "TXT-XXX" where XXX is a specific month. So I would like to have it dynamic. I tried using wildcards to represent "XXX" but it keeps giving me a bad file name error. Currently if there is no value, it opens the explorer to look for a file. that part works and I can import the txt files just fine. but the dynamic part does not work.

the possible XXX file names are: "JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"

Here is the part of the code that I am refering to.

    Dim my_file As Integer
    Dim text_line As String
    Dim File_Name As String
    Dim i As Integer
    Dim worknumber As String
    
    worknumber = Range("K5").Value
    
    If worknumber = "" Then
        File_Name = Application.GetOpenFilename
        
    Else
    File_Name = "U:TXT-***" & worknumber & ".txt"
    
    End If

    my_file = FreeFile()
    Open File_Name For Input As my_file

    i = 1

    While Not EOF(my_file)
        Line Input #my_file, text_line
        Cells(i, "A").Value = text_line
        i = i + 1
    Wend

question from:https://stackoverflow.com/questions/66050735/get-vba-code-in-excel-to-find-a-file-and-open-it

Answer 1

Tolga,

You need a structure like this:

Option Explicit

Sub Test()

   Dim zFile_Name  As String
   Dim zFile_Found As String
   Dim iWorkNumber As Integer
   
   iWorkNumber = 345  '*** For testing only!
   
      zFile_Name = "U:TXT-???" & iWorkNumber & ".txt"
      zFile_Found = Dir(zFile_Name)
   
   If zFile_Found <> "" Then
     '*** Process your file here ***
      MsgBox "U:" & zFile_Found & " FOUND!", vbOKOnly + vbInformation, _
               "File ready for processing:"
   Else
    '*** Process the File Not Found error here ***
      MsgBox "G:TXT-MMM" & iWorkNumber & ".txt" & " not found!", _
             vbOKOnly + vbCritical, "File Not Found:"
   End If
   
End Sub 'Test()

The above assumes that the Work Numbers are not present in more than one month. If that is the case additional processing will be required as this code will only return the first match of any (MMM) month value. It also assumes that there are no non-month value files of the same structure in the directory, e.g. U:TXT-XYZ345.txt.