CODEWITHSUNDEEP
excelvba
Jaz
Jaz

Reputation: 55

VBA - How to search Active Workbooks, and match a filename against string?

I am coding up a quick automation project for work, I cannot specify a filepath to open a workbook from as it is used in a special software, not easily accessible with VBA.

If User A opens this file: "ABC_todaysdate"... How would I tell Excel to cycle through the active workbooks (User A could have 5-6 workbooks open), find the letter ABC in the Activeworkbook filename, and use that in the rest of my function?

VBA Code so far:

Sub CopyDemand()
Dim filename As String
Dim Wb As Workbook
Dim ws As Worksheet
Dim Wb2 As Workbook

Set Wb = ThisWorkbook
For Each Wb2 In Application.Workbooks
filename = ActiveWorkbook.FullName
If filename Like "demand" Then
Debug.Print ("Found")
''' Insert function to use WB2 and copy over data, compare workbooks etc.
Next
Wb.Activate

End Sub

Upvotes: 1

Views: 2672

Answers (2)

Ryan Wildry
Ryan Wildry

Reputation: 5677

You can iterate each workbook, however I think the key here is to make the workbook active so you can retrieve the FullName with the path information.

Option Explicit

Function getWbName(SearchStr As String) As String
On Error GoTo ErrHand:
    Application.ScreenUpdating = False
    Dim wb As Workbook

    getWbName = vbNullString

    For Each wb In Workbooks
        If InStr(1, wb.Name, SearchStr, vbTextCompare) > 0 Then
            wb.Activate
            getWbName = ActiveWorkbook.FullName
            Exit For
        End If
    Next

    'Return the active window and exit
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
    Exit Function

ErrHand:
    Application.ScreenUpdating = True
    MsgBox (Err.Number & " has occured, with description: " & Err.Description)
End Function

Sub Example()
    Debug.Print getWbName("Book2")
End Sub

EDIT

Updated the code above to return the WorkBook Object instead.

Option Explicit

Function getWorkBookByName(SearchStr As String) As Workbook
    Dim wb As Workbook

    For Each wb In Workbooks
        If InStr(1, wb.Name, SearchStr, vbTextCompare) > 0 Then
            Set getWorkBookByName = wb
            Exit Function
        End If
    Next
End Function

Sub Example()
    Dim myWb As Workbook: Set myWb = getWorkBookByName("Book2")
    If Not myWb Is Nothing Then Debug.Print myWb.FullName
End Sub

Upvotes: 2

Vincent G
Vincent G

Reputation: 3188

You can try to capture the WorkbookOpen event of the Application object.

Here is a way to do so: copy the following code into the ThisWorkbook object of your main workbook. 

Option Explicit

Private WithEvents myApp As Application

Private Sub myApp_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "Openned " & Wb.FullName
End Sub

Private Sub Workbook_Open()
    Set myApp = Application
End Sub

Then close you workbook and reopen if with macro enabled. The name of this workbook and each subsequent workbook openned whil it is open will be shown in a message box.

Upvotes: 0

Related Questions