CODEWITHSUNDEEP
vbaexcel
Daheb
Daheb

Reputation: 35

Retrieving the last modified file with a partly variable name

We have a system that automatically downloads data and puts it in excel and other sheets. I am trying to write a macro for a master spreadsheet that retrieves the latest version of a certain file to edit, copy and paste into the master sheet.

I have trouble retrieving the file as the filenames include dates.

I am quite new to VBA and am still just throwing pieces of code together to get a working thing, but I cannot find exactly what I am looking for.

Filename is for example 'ML0003 - Daily Order Entry Information - 170927' The last 6 figures represent the date and changes every time.

This is my code so far:

Dim dtTestDate As Date
    Dim sStartWB As String

    Const sPath As String = "D:\Berry\AddIn\Testing\"
    Const dtEarliest = #1/1/2010#

    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name

    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & "ML0003 - Daily Order Entry Information - " & " ****** " & ".xls"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend

    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."

I was under the assumtion that the asterix would allow any character there, but this does not seem to work. Any ideas?

Upvotes: 3

Views: 222

Answers (1)

braX
braX

Reputation: 11755

You will want to use the Dir function to look for a file using the wildcard, like this:

Dim sFilename As String
While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
    sFilename = Dir(sPath & "ML0003 - Daily Order Entry Information - *.xls*")
    If sFilename <> "" Then Workbooks.Open sPath & sFilename
Wend

Upvotes: 1

Related Questions