importing text file with unknown characters in title

*benj · 07-14-2010, 11:28 AM

Hi,

I'm new to VBA but I've been getting on well using excels macro recorder and working out what aspects of the VBA formula relate to which functions being performed or typing in problems in my search engine to get answers....

however i've been stuck on the following for a few hours now.

I have an excel file with a date always in a particular cell, lets say A1, sheet 1, the date format is yy/mm/dd although this could easily be altered. I need to ask VBA to take this date and search a given filepath for a filename with this particular date in the title.
The titles will be of the format: time_yy/mm/dd.txt
i.e. they will begin with 4 characters defining the time which will vary and be unimportant, then the date characters which is the basis on which I need to search to be performed.

How do I ask VBA to search for a file title where some of the characters are unknown.

I then need it to import the file into my active excel spreadsheet.

Any help would be greatly appreciated, I've tried a few variations of code but can't seem to work it out alone.

Ben

**Leith Ross** · 07-14-2010, 12:32 PM

Hello Ben,

Welcome to the Forum!

This macro will search a folder for all files that have match this pattern "_yy/mm/dd.txt". The matching files will be listed on "Sheet1" starting with cell "A1". Change the worksheet and file path in the macro to what you want to use. They are marked in bold.

'Written: July 14, 2010
'Author:  Leith Ross
'Summary: Lists files on a worksheet in column "A" that match a pattern.

Sub ListFileNames()

  Dim FileName As String
  Dim FilePath As String
  Dim Valid As Boolean
  Dim Wks As Worksheet
  
    R = 1    'Starting Row
    Set Wks = Worksheets("Sheet1")
    FilePath = "C:\\My Documents\" & "*.txt"
       
     FileName = Dir(FilePath)
     
     Do While FileName <> ""
       Valid = FileName Like "*_##/##/##.txt"
         If Valid Then
           If Mid(FileName, 6, 5) = Acct Then
             Wks.Cells(R, "A") = FileName
             R = R + 1
           End If
         End If
       FileName = Dir
     Loop
     
End Sub

*benj · 07-15-2010, 05:08 AM

Thanks Leith, that code would be just the sort of the I could put into my macro,

Unfortunately I seem to be struggling to get it to work,

I realised the date format of the file title can't have the / symbol in it, so the files are in time_yymmdd.txt format instead. I altered your code slightly to account for this.

As I understand it if I have the following 3 files in the directory C:\test\
1215_100609.txt
1109_100722.txt
1845_090420.txt

then these 3 titles should be entered into cells A1, A2, A3 of sheet 1?

I ran the slightly altered below code but nothing appeared.


Sub ListFileNames()

  Dim FileName As String
  Dim FilePath As String
  Dim Valid As Boolean
  Dim Wks As Worksheet
  
    R = 1    'Starting Row
    Set Wks = Worksheets("Sheet1")
    FilePath = "C:\test\" & "*.txt"
       
     FileName = Dir(FilePath)
     
     Do While FileName <> ""
       Valid = FileName Like "*_######.txt"
         If Valid Then
           If Mid(FileName, 6, 5) = Acct Then
             Wks.Cells(R, "A") = FileName
             R = R + 1
           End If
         End If
       FileName = Dir
     Loop
     
End Sub

Do you have any suggestions to what I might have done wrong?

**Leith Ross** · 07-15-2010, 02:39 PM

Hello *benj,

You had the part that writes the file name to the worksheet in an IF statement checking it against the variable Acct. Since Acct was not set to anything, nothing ever matched. I corrected the code.

Sub ListFileNames()

  Dim FileName As String
  Dim FilePath As String
  Dim Valid As Boolean
  Dim Wks As Worksheet
  
    R = 1    'Starting Row
    Set Wks = Worksheets("Sheet1")
    FilePath = "C:\test" & "*.txt"
       
     FileName = Dir(FilePath)
     
     Do While FileName <> ""
       Valid = FileName Like "*_######.txt"
         If Valid Then
            Wks.Cells(R, "A") = FileName
            R = R + 1
            Acct = Mid(FileName, 6, 6)
         End If
       FileName = Dir
     Loop
     
End Sub