VBA Open workbook with wildcard?

Question

I have a .xlsx file in the following directory:

G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\8. 2017\2017 Planner.xlsx

I could just point it to this directory but the directory will change depending on the year it is.

So this directory could become:

G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\9. 2018\2018 Planner.xlsx

To handle this i am trying to add wildcards to my path like so:

G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\" & "*." & " " & Year(Date) & "\"

My workbook will not open. Please can someone show me where i am going wrong?

Sub:

'Find Planner
If Len(FindDepotMemo) Then
Set wb2 = Workbooks.Open(FindDepotMemo, ReadOnly:=True, UpdateLinks:=False)
End If

Function:

Function FindDepotMemo() As String

    Dim Path As String
    Dim FindFirstFile As String

    Path = "G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\" & "*." & " " & Year(Date) & "\"

    FindFirstFile = Dir$(Path & "*.xlsx")

    While (FindFirstFile <> "")

        If InStr(FindFirstFile, "Planner") > 0 Then

            FindDepotMemo = Path & FindFirstFile
            Exit Function

        End If

        FindFirstFile = Dir

    Wend

End Function

This seems to be a fair answer to your question. http://stackoverflow.com/questions/19527415/using-a-wildcard-to-open-an-excel-workbook?rq=1 — Variatus, Apr 06 '17 at 14:21
I don't understand why you don't know the *exact* file you want to open? Surely you take an input from the user like `strYearOfInterest` and then use that input to set a variable which you pass into the string for the file name ? — Robin Mackenzie, Apr 06 '17 at 14:22
@Variatus thanks i tried this but i think its because im using wildcard with number and its not the same — user7415328, Apr 06 '17 at 14:22
Does your last folder should be `8. 2017`? Can't you have it as only `2017`? Because, everything works fine without that `8.`. — ManishChristian, Apr 06 '17 at 14:42
@ManishChristian unfortunately not, i cannot alter the folders :( — user7415328, Apr 06 '17 at 14:47
Will that folder get incremented number along with year? ie. `8. 2017`, `9. 2018`, `10. 2019` — ManishChristian, Apr 06 '17 at 15:02

score 0 · Answer 1 · answered Apr 06 '17 at 15:45

Try this approach. The code loops through folder names 1. 2020, 1. 2019, 1. 2018 and current year, finding the lowest one, say, 1. 2018 exists. For that year it then looks for the highest month number, abandoning the previous for the next higher. If, say, 4. 2018 exists that is the path in which it looks for the file name using your original code.

Function FindDepotMemo() As String

    Dim Pn As String                                ' Path name
    Dim Fn As String                                ' Folder name
    Dim Sp() As String                              ' Split string
    Dim Arr() As String, Tmp As String
    Dim FindFirstFile As String
    Dim i As Integer
    Dim n As Integer

    For i = 2020 To Year(Date) Step -1
        Pn = "G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\1. " & CStr(i) & "\"
        If Len(Dir(Pn, vbDirectory)) Then Exit For
    Next i

    If i >= Year(Date) Then
        Sp = Split(Pn, "\")
        Arr = Sp
        n = UBound(Sp) - 1
        Fn = Sp(n)
        For i = 2 To 12
            Arr(n) = CStr(i) & Mid(Fn, 2)
            Pn = Join(Arr, "\")
            If Len(Dir(Pn, vbDirectory)) = 0 Then Exit For
            Sp = Arr
        Next i

        FindFirstFile = Join(Sp, "\") & "*.xlsx"
        While (FindFirstFile <> "")
            If InStr(FindFirstFile, "Planner") > 0 Then
                FindDepotMemo = Pn & FindFirstFile
                Exit Do
            End If
            FindFirstFile = Dir
        Wend
    End If
End Function

If no 1. 2017 or higher is found, the function returns a null string. I couldn't test the code for lack of data.

score 0 · Accepted Answer · answered Apr 06 '17 at 16:30

There are many ways but, I would use this approach:

Function FindDepotMemo() As String

    Dim oldPath, tempPath, newPath As String
    Dim FindFirstFile As String
    Dim addInt as Integer

    ' ASSUMING YOU ALREADY HAVE THIS PATH
    ' WE WILL USE THIS AS BASE PATH
    ' AND WE WILL INCREMENT THE NUMBER AND YEAR IN IT
    oldPath = "G:\BUYING\Food Specials\2. Planning\1. Planning\1. Planner\8. 2017"

    ' EXTRACT 8. 2017 FROM oldPath
    tempPath = Mid(oldPath, InStrRev(oldPath, "\") + 1)

    ' GET THE YEAR DIFFERENCE
    addInt = Year(Date) - CInt(Split(tempPath, ".")(1))

    ' ADD THIS DIFFERENCE TO NUMBER AND YEAR
    ' AND NOW YOU HAVE CURRENT YEAR FOLDER
    newTemp = Split(tempPath, ".")(0) + addInt & ". " & Split(tempPath, ".")(1) + addInt

    FindFirstFile = Dir$(Path & "\" & "*.xlsx")

    While (FindFirstFile <> "")
        If InStr(FindFirstFile, "Test") > 0 Then
            FindDepotMemo = Path & FindFirstFile
            Exit Function
        End If
        FindFirstFile = Dir
    Wend

End Function

As I've added comments to help you understand what am I doing.

VBA Open workbook with wildcard?

2 Answers2