Make macro dynamically call workbooks

user_14494200 0
Viewed 42

I have files I download on a daily basis but the name of the file changes every day.

Ex: Day 1 file name: miss123_1 Day 2: miss349_1

Is there any way to have the macro look for open files where the active workbooks title contains certain characters rather than having the macro look for that exact file? My current workaround is to constantly change the workbook title to fit that days name but there must be a way to make it dynamic.

My idea that does not work:

Windows(Contains("miss","_1",".xlsx")).Activate
vba excel
1 Answers

Reference an Open Workbook by Partial Name

  • You need to loop through the Workbooks collection and compare each workbook name to a string pattern.
  • You can use the Like operator for the comparison.
  • The RefWorkbookTEST procedure illustrates how to utilize the RefWorkbook function.
Option Explicit

Sub RefWorkbookTEST()
    
    Dim wb As Workbook: Set wb = RefWorkbook("miss*_1*.xlsx")
    
    If wb Is Nothing Then
        MsgBox "Workbook not found.", vbExclamation
    Else
        MsgBox "Referenced workbook '" & wb.Name & "'.", vbInformation
    End If

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      References an open workbook whose lower-case name matches
'               a given lower-case string pattern.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefWorkbook(ByVal LowerCaseNamePattern As String) As Workbook
    Dim wb As Workbook
    For Each wb In Workbooks
        If LCase(wb.Name) Like LowerCaseNamePattern Then
            Set RefWorkbook = wb
            Exit Function
        End If
    Next wb
End Function
user_98140690
Related