CODEWITHSUNDEEP
excelvba
jmano
jmano

Reputation: 29

Make macro dynamically call workbooks

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

Upvotes: 2

Views: 49

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

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

Upvotes: 3

Related Questions