vba try finding a directory using many variants of

Question

I am using vba to try and locate a file that changes name ever so slightly each couple of days. This file is stored on a local intranet system.

The filename is always:

Food Specials Rolling Depot Memo xx - xx.xlsm

Where xx could represent a different week number e.g.

03 - 21
22 - 52
etc..

So one day the file may look like:

Food Specials Rolling Depot Memo 03 - 21.xlsm

The next day it may look like:

Food Specials Rolling Depot Memo 22 - 52.xlsm

Is there any way i could create 2 strings which contain all my week numbers, (i.e. 01 - 52) and test each of these at random within the file path like so:

     WeekNumber1 = 1,2,3,4,5,6,7,8,9,10 etc.
    WeekNumber2 = 1,2,3,4,5,6,7,8,9,10 etc.



    Set wb = Workbooks.Open(sURL & "Food%20Specials%20Rolling%20Depot%20Memo%20" & WeekNumber1 & "%20-%20" & WeekNumber2 & ".xlsm")

MsgBox wb

On Error GoTo 0

Thanks

Answer 1

a proposition, as I am not sure that the dir() function does work on sharepoint/internet site

Sub test()
    On Error Resume Next
    For i = 1 To 52
        For j = i To 52

            Set wb = Workbooks.Open(sURL & "Food%20Specials%20Rolling%20Depot%20Memo%20" & format(i,"00") & "%20-%20" & format(j,"00" & ".xlsm")
            If Not wb Is Empty Then Exit For
        Next j
        If Not wb Is Empty Then Exit For
    Next i
    If wb Is Empty Then
        MsgBox "could not find file"
    Else
        MsgBox "found " & wb.Name
    End If
End Sub

Answer 2

You can use the Dir function to iterate over all files matching your wildcard:

Dim wildcard as String
Dim fileName as String

' Prepend a path to look in a directory different from the current directory
wildcard = "Food Specials Rolling Depot Memo ?? - ??.xlsm"
fileName = Dir(wildcard)
Do While fileName <> ""
    ' Process fileName
    Set wb = Workbooks.Open(fileName)

    fileName = Dir()
Loop

Answer 3

Here is a simple way using Like:

Sub GetTheName()
    Dim s As String, FileName As String

    s = "C:\TestFolder\*.xlsm"

    FileName = Dir(s)

    Do Until FileName = ""
        If FileName Like "Food Specials Rolling Depot Memo*" Then MsgBox FileName
        FileName = Dir()
    Loop
End Sub