Find specific file by naming convention

A

Atroxell

Active Member
Joined
Apr 18, 2007
Messages
422
I have a weekly compilation report in which I take what could be daily reports (sometimes skipping a day or two) and copies their contents into a single workbook. Once each of the daily files have been compiled into a single worksheet, the workbook is saved as a .xls file.

I have been doing this manually for about a year, but I now want to automate it to make it less mindnumbing.

My difficulty comes from the naming conventions used for the file names. The file name starts with a date/time stamp, and finishes with a long name and a .TXT extension. For example:

Code: 
20090729004101_AKMGH1875_CARDS_SEND.TXT
20090731013135_AKMGH1875_CARDS_SEND.TXT

Now, I have been able to find tons of examples on how to find .TXT files in a folder, but have not been able to modify them to look for the final sequence of characters in the file names. I would like to peel just those files meeting the naming convention specifications like the file above.

There will always be other .TXT files in the folder, but they will have different name conventions. Can anyone help me define a way to find the files I need then load them to an array so I can process them?

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
What is the naming convention?
 
Upvote 0
Atroxell

Assuming you are not using Excel 2007, see if this is any use.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Collect_file_names()<br>    <SPAN style="color:#00007F">Dim</SPAN> mypath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, fullname <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, name <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> mynames() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    mypath = "C:\Mr Excel\" <SPAN style="color:#007F00">'<-- adjust to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application.FileSearch<br>        .NewSearch<br>        .LookIn = mypath<br>        .SearchSubFolders = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#007F00">'Adjust the line below to include the text that is common to each filename</SPAN><br>        .Filename = "*AKMGH1875_CARDS_SEND.txt"<br>        .MatchTextExactly = <SPAN style="color:#00007F">True</SPAN><br>        .FileType = msoFileTypeAllFiles<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application.FileSearch<br>        <SPAN style="color:#00007F">If</SPAN> .Execute() > 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN> mynames(.FoundFiles.Count)<br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> .FoundFiles.Count<br>                fullname = .FoundFiles(i)<br>                name = Right(fullname, Len(fullname) - Len(mypath))<br>                mynames(i) = name<br>             <SPAN style="color:#00007F">Next</SPAN> i<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Upvote 0
Thanks to both of you for the response!

Norie--The naming convention follows the two file name examples I posted:

Code: 
20090729004101_AKMGH1875_CARDS_SEND.TXT
20090731013135_AKMGH1875_CARDS_SEND.TXT

Peter--I am running 2007, and I should have stated such. The sampels you posted are recognizable as examples similar to the others I have found on multiple posts around the Web that I assume are 2003 VBA.

I am guessing something changed in 2007, but I have not been able to deduce the correct syntax.

Once I get this down I guess maybe it's time for me to (finally) break down and buy that VBA coding manual? :)
 
Upvote 0
Could you explain in words what the naming convention is?

The numbers after the date don't, to me anyway, look like times.:)
 
Upvote 0
Atroxell said:
Peter--I am running 2007, and I should have stated such. The sampels you posted are recognizable as examples similar to the others I have found on multiple posts around the Web that I assume are 2003 VBA.

I am guessing something changed in 2007, ...
Click to expand...
Yes Application.FileSearch is not available in Excel 2007.

Borrowing heavily from Richard Schollar's code, which is linked to from this thread, I have put together this code that puts all the txt files that end with "_AKMGH1875_CARDS_SEND.txt" from the folder "C:\Mr Excel" into an array called myList. I hope that is the sort of thing you are after. There is some Message Box stuff in there that you can get rid of once you are comfortable that the code is doing what you want.

VBA Code: 
Sub fList()
  Dim myList
  Dim fldr As String, fltr As String, sTemp As String, sHldr As String
  Dim i As Long
  Dim msg As String
  
  fldr = "C:\Mr Excel"
  If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
  fltr = "*_AKMGH1875_CARDS_SEND.txt"
  msg = fltr & " files found:" & vbLf
  sHldr = Dir(fldr & fltr)
  Do While sHldr <> ""
    sTemp = sTemp & "|" & sHldr
    sHldr = Dir
  Loop
  If sTemp <> "" Then
    myList = Split(sTemp, "|")
    For i = 1 To UBound(myList)
      msg = msg & vbLf & myList(i)
    Next i
  Else
    msg = msg & vbLf & "None"
  End If
  MsgBox msg
End Sub
 
Last edited:
Upvote 0
Try this code:

Code: 
Sub OpenAllFiles()
Dim YourFile As Variant
Dim YourFolderPath As Variant
    YourFolderPath = "..." 'update this
    ChDir YourFolderPath
    YourFile = Dir(YourFolderPath & "*AKMGH1875_CARDS_SEND.TXT")
    Do While YourFile <> ""
        Workbooks.Open Filename:=YourFile
        YourFile = Dir
    Loop
End Sub
 
Upvote 0
Perfect! That last example was just the trick. I'm sure I will be using that bit of code over and over from now on.

Thank you all for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Replace different text/strings in multiple files in one folder
Replies
1
Views
256
Peter_SSs
Peter_SSs
J
  • Question
Automatically import .txt files into separate sheets in Excel
Replies
1
Views
183
John_w
J
D
  • Solved
Export and name sheets within a workbook to a text file in same directory as workbook
Replies
3
Views
515
rlv01
R
S
  • Solved
Search for file names that are not in a specific folder provided that their name is in a column
Replies
2
Views
209
sofas
S
R
  • Question
VBA Code to delete files in a folder that do not contain a specific text string
Replies
3
Views
230
Micron
M

Forum statistics

Threads
1,214,891
Messages
6,122,101
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top