2

I have some files that are called Team-(Random Number).txt. 

Dir("Team-" & "*" & ".txt")

But when there have been changes there could be text files called Team-(Random Number)-AAA.txt, Team-(Random Number)-AAB.txt and so but the most recent file is always called Team-(Random Number).txt.

Since Dir only returns 1 file and does this randomly is there a way to get the file Team-(Random Number).txt?

It should be no problem if dir returned the result in a normal order but apparently it does it randomly.

I've thought of excluding the -AAA part but don't know what the syntax should. Or in a less efficient way to get all files and sort it in an array but with 10 - 200 files it's not very efficient.

Now I'm hoping could give me the syntax of excluding the part or other workaround for my problem thanks!

Community
  • 1
  • 1
Xepos
  • 167
  • 1
  • 15

3 Answers3

3

I'd say go for Regular Expressions.

Private Sub TeamTxtExists()

    Dim Path As String, Pattern As String, FileFound As String
    Dim REGEX As Object, Matches As Object
    Dim oFSO As Object, oFolder As Object, oFile As Object

    Path = "D:\Personal\Stack Overflow\" 'Modify as necessary.
    Pattern = "(Team-(\d+).txt)"
    Set REGEX = CreateObject("VBScript.RegExp")
    With REGEX
        .Pattern = Pattern
        .Global = True
        .IgnoreCase = True
    End With

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(Path)
    For Each oFile In oFolder.Files
        Set Matches = REGEX.Execute(oFile.Name)
        For Each Match In Matches
            Debug.Print Match.Value
        Next Match
    Next oFile

End Sub

This will print in your immediate window (Ctrl-G in the VBE) all the names of text files that don't have AAA or the like in their filenames. Tried and tested.

WGS
  • 13,969
  • 4
  • 48
  • 51
  • Probably an overkill solution but +1. :) FYI: Reference to FSO is not needed if you use late-binding: `Dim oFolder as Object, oFile as Object`. (Incidentally, this is the same reason why you don't say they need a reference to the RegExp!) – David Zemens Dec 06 '13 at 17:31
  • 1
    Thanks, @DavidZemens! I honestly forgot that, what with the excitement of using a neutron star to utterly destroy a housefly. ;) – WGS Dec 06 '13 at 17:37
  • Hey, @brettdj! I know `Dir` is going to be the best solution but my approach was to show a potential alternative to more complicated patterning. ;) And as I said in the comments, it **is** overkill. :) – WGS Dec 07 '13 at 10:40
  • +1 for the regexp - amd the neutorn star comment. The overkill bit was with reference to testing each file in the directory – brettdj Dec 08 '13 at 20:15
2

In a similar vein to Loop through files in a folder using VBA?

  1. Use Dir to efficiently find the first group of files that match team-xxxx.txt
  2. Then zero in on the wanted match which could either be done with
    • Like for a simple match
    • Regexp for a harder match
  3. Exit the Dir list on a successful match

I went with the regexp.

code

    Sub LoopThroughFiles()
    Dim objRegex As Object
    Dim StrFile As String
    Dim bFound As Boolean

    bFound = False
    Set objRegex = CreateObject("VBScript.RegExp")
    objRegex.Pattern = "team-\d+"

    StrFile = Dir("c:\temp\team-*.txt")
    Do While Len(StrFile) > 0
        If objRegex.test(StrFile) = False Then
        StrFile = Dir
        Else
        bFound = True
        MsgBox "Your file is " & StrFile
        Exit Do
        End If
    Loop
    If Not bFound Then MsgBox "No Match", vbCritical
End Sub
Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177
0

Is this helping?:

Dir("Your_folder_path_ending_with_a_\" & "Team-(*).txt")

Going a bit more in-depth and using the folder content shown in the picture:

enter image description here

This sub will return all the file names that only contain "Team-(Random Number).txt":

Sub showFileName()
    Dim FolderPath As String: FolderPath = "C:\test\"
    Dim Filter As String: Filter = "Team-(*).txt"
    Dim dirTmp As String

    dirTmp = Dir(FolderPath & Filter)

    Do While Len(dirTmp) > 0
        Debug.Print dirTmp
        dirTmp = Dir
    Loop
End Sub

The result is:
Team-(123).txt
Team-(14).txt
Team-(PI).txt

simpLE MAn
  • 1,582
  • 13
  • 22
  • Very simple and nice approach. However, one issue I can see with this is if OP is requesting solely for numbers inside the `()`. Obviously, `Team-(PI)` will not fit the request. ;) – WGS Dec 07 '13 at 02:13
  • @BK201 Thanks. Ok but you could just declare a variant which could take any combinations of "*", "?", "random number" and replace `"Team-(*).txt"` by `"Team-(" & varFilter & ").txt"`. – simpLE MAn Dec 08 '13 at 13:05