CODEWITHSUNDEEP
excelvbawildcard
mmichaelx
mmichaelx

Reputation: 95

Return value of filename wildcard

I've attempted to research this around the interwebz but to no avail.

I have this code that searches a folder to see if a file exists. The problem is I don't know the complete file name so I used a wildcard. The file with wildcard "Provider*_extra"&".csv" - the wildcard represents a date and a string - for example Provider_20131126_purple_extra.csv.

I want to know what that wildcard value is - e.g. I want to call the "20131126_purple" or whatever it is. Does anyone know how to do this in VBA? Thank you in advance.

FileName = Dir$(MyPath & "PROVIDER*_EXTRA" & ".csv")
If (Len(FileName) > 0) Then

wb1.Sheets("Found Files").Activate
LastRow = Range("E" & Rows.Count).End(xlUp).Row + 1
Range("E" & LastRow).Activate
ActiveCell = "PROVIDER EXTRA FILE"
wb1.Sheets("Control").Activate

Else

End If

Upvotes: 3

Views: 535

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

This will do what you want.

I have this pair of functions which I wrote and use all the time:

Function GetFilenamesMatchingPattern(ByVal pathPattern As String,  _
    Optional attributes As VbFileAttribute = vbNormal) As String()
    Dim i As Long
    Dim nFiles As Long
    Dim filenames() As String
    nFiles = CountFilesMatchingPattern(pathPattern, attributes)
    If nFiles > 0 Then
        ReDim filenames(1 To nFiles)
        filenames(1) = dir(pathPattern, attributes)
        For i = 2 To nFiles
            filenames(i) = dir()
        Next i
    Else
        'Return unallocated array
    End If
    GetFilenamesMatchingPattern = filenames
End Function
Function CountFilesMatchingPattern(ByVal pathPattern As String,  _
    Optional attributes As VbFileAttribute = vbNormal) As Long
    Dim nFiles As Long
    If dir(pathPattern, attributes) = "" Then
        nFiles = 0
    Else
        nFiles = 1
        Do While dir() <> ""
            nFiles = nFiles + 1
        Loop
    End If
    CountFilesMatchingPattern = nFiles
End Function

Apologies if anyone doesn't like the style!

Example usage with wildcard:

Dim filenames() As String
filenames = GetFilenamesMatchingPattern("C:\Users\myName\Documents\Book*.xlsm")

'returns an array of Strings:
'{"Book1.xlsm", "Book2.xlsm", "Book2_test.xlsm"}

Now for your specific problem, namely getting the various wildcard values:

Dim i As Long
Dim filenames() As String
Dim wildcardValues() As String
Dim part1 As String
Dim part2 As String
part1 = "Book" ' yours would be "Provider_"
part2 = ".xlsm" ' yours would be "_extra.csv"
filenames = GetFilenamesMatchingPattern("C:\Users\myName\Documents\" _
    & part1 & "*" & part2)
ReDim wildcardValues(LBound(filenames) To UBound(filenames))
For i = LBound(filenames) To UBound(filenames)
    wildcardValues(i) = Replace(Replace(filenames(i), part1, ""), part2, "")
Next i
'wildcardValues is now:
'{"1", "2", "2_test"}
'Done.

Upvotes: 2

user2140173
user2140173

Reputation:

you can use

Debug.print FileName to get the name of the file you're currently using.

if you want to loop through all files in a folder please see this

short sample:

Public Const PathToFolder As String = "C:\SampleFolder\"

Sub PrintFilesNames()
    Dim file As String
    file = Dir$(PathToFolder)
    While (Len(file) > 0)
        Debug.Print file
        file = Dir
    Wend
End Sub

Upvotes: 1

Michael
Michael

Reputation: 538

You can try it with the REPLACE function:

wildcard = Replace(FileName, "Provider", "")
wildcard = Replace(wildcard, "_extra.csv", "")

Upvotes: 2

Related Questions