CODEWITHSUNDEEP
excelvba
Xepos
Xepos

Reputation: 167

Use Dir to find file without "AAA"

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!

Upvotes: 2

Views: 252

Answers (3)

brettdj
brettdj

Reputation: 55672

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

Upvotes: 2

WGS
WGS

Reputation: 14169

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.

Upvotes: 3

simpLE MAn
simpLE MAn

Reputation: 1622

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

Upvotes: 0

Related Questions