[FIXED] How to find, open, and refer to workbook with partial name

Issue

I am trying to use a partial name match to locate a template file (full name On-Call Audit Sheet VXX where VXX is the version) that gets updated from the current workbook with the macro I am writing.

The macro needs to locate the file with a partial name match; if found then open it and define the workbook as wb1, if not found then return an error. Current code below partially inspired by this post.

So far the macro can locate and open the file with partial name match using the FileSystemObject to grab the current folder path, but I can't work out how to then define wb1 with the partial name match.

Is there a way to get the full name of the file once the partial match is successful and thus define wb1 from that?

Sub anotherTest()

    Dim fso As FileSystemObject
    Dim fldBase As Folder

    Dim wb1 As Workbook
    Dim ws1 As Worksheet

    Set fso = New FileSystemObject
    'determining current folder location based on where the dashboard file is
    Set fldBase = fso.GetFolder(ThisWorkbook.Path)

    For Each Item In fldBase.Files
        If InStr(Item.Name, "*Audit Sheet*") Then
            Workbooks.Open (fldBase & "\" & Item.Name) '<-- Open workbook
            Set wb1 = Workbooks(fldBase & "\" & Item.Name) '<-- set workbook to wb1, THIS BIT DOESNT WORK
        Else
            MsgBox "File not found" '<-- if not found exit sub after showing error
            Exit Sub
        End If
    Next

    'Rest of the macro

End Sub

Solution

Your code currently works on the basis that there is only ever one file that matches your *Audit Sheet* pattern. If there are 2 or more, then it will open them all but only point wb1 at the latest.

I assume this isn't what you want.

The following will open the first that it finds (so you might want to tighten up your pattern?) :

Sub Test()

    Dim fldBase As String
    Dim filefound As String

    Dim wb1 As Workbook
    Dim ws1 As Worksheet

    fldBase = "C:\yourbasefolder"
    filefound = Dir(fldBase & "\" & "*Audit Sheet*.xlsm")

    If filefound = "" Then
        MsgBox "File not found" '<-- if not found exit sub after showing error
        Exit Sub
    Else
        Set wb1 = Workbooks.Open(fldBase & "\" & filefound)
    End If

    'Rest of the macro

End Sub

Answered By - CLR

Answer Checked By - Robin (PHPFixing Admin)