Find similarities in different workbook names

[kgartin]

I have some pretty cool code set up that will automatically run when a workbook opens depending on the file name. I want to be able to compare each word of each name in two different workbooks and if any of the words in the name match, do something.

EX1:

Workbook one is named, "FL HOME MARKET 1-21-19"
Workbook two is named, "FLORIDA HOME MKT 1-21-19"

Code fires when "HOME" is matched between two workbooks

EX2:

Workbook one is named, "FLA HOM MARKET 1-21-19"
Workbook two is named, "FLORIDA HOME MARKET 1-21-19"

Code fires when "MARKET" is matched between two workbooks

The names might change at times so I want it to compare each word and if any match then fire.


Right now I have everything set to match the names based on the first word using LEFT but it's clunky and I usually just end up changing the file names before I open them.


(JUSY FYI: I regularly work with different reports from different departments that are named similarly but not exactly identical)

 

[DanteAmor]

You have a macro that is executed when you open the book, and I suppose that compares the name of the book, but against which another book name should be compared, with another book that you already have open?
If the above is correct, then try the following:

Private Sub Workbook_Open()
    arr1 = Split(ThisWorkbook.Name, " ")
    For Each wbook In Application.Workbooks
        nombre = wbook.Name
        If nombre <> ThisWorkbook.Name Then
            arr2 = Split(nombre, " ")
            For i = LBound(arr1) To UBound(arr1)
                If InStr(1, arr1(i), "-") = 0 Then
                    For j = LBound(arr2) To UBound(arr2)
                        If arr1(i) = arr2(j) Then
                            'fire
                            'put your macro here to do something
                            '
                            MsgBox "The word that matches is : " & arr1(i)
                            Exit Sub
                        End If
                    Next
                End If
            Next
        End If
    Next
End Sub

 

[kgartin]

Thanks Dante! I'll Save your code for review for future application! Looks like it may be useful!

I figured it out actually with some digging and piecemeal-ing so, for the sake of anyone else who may be lurking and needing an answer, this is what I came up with. Some of this may look weird but it's all necessary per my application:

Dim WB As Workbook, AWN, TRAWN As String, TRAWNB As String, A As Integer, B As Integer, WBN() As String, WBNCOUNT As Integer

WBC = Application.Workbooks.Count
AWN = ActiveWorkbook.Name
TRAWN = Replace(AWN, Right(AWN, Len(AWN) - Application.Find(".", AWN) + 1), "")
TRAWNB = Application.Trim(TRAWN)


If IsNumeric(Right(TRAWNB, 2)) Then
    A = 0
    WBN = Split(TRAWNB, " ", 10)
Else
    A = 1
    WBN = Split(TRAWNB, " ", 10)
End If


WBNCOUNT = UBound(WBN) - LBound(WBN) + 1


B = 1


For Each WB In Application.Workbooks
   If WB.Name = AWN Then GoTo SKIPWB
        If WB.Name = "METRA ELECTRONICS FULL RUNSHEET.xlsm" Then GoTo SKIPWB
        If InStr(1, WB.Name, WBN(A), vbTextCompare) Then
            WB.Activate
            WB.Worksheets("BILLING FORM (NEW)").Range("AT5:AU30") = Workbooks(TRAWN).Worksheets("BILLING FORM (NEW)").Range("AT5:AU30").Value
            Workbooks(Year(Date) & " Com Forecast (544).xlsm").Worksheets("START DATE").Range("V5") = False
            Call PASTEPREP_PASTE
        ElseIf InStr(1, WB.Name, WBN(A), vbTextCompare) = False Then
            If WBNCOUNT = 1 Then
                B = B + 1
                GoTo SKIPWB
            End If
            If InStr(1, WB.Name, WBN(A + 1), vbTextCompare) Then
                WB.Activate
                WB.Worksheets("BILLING FORM (NEW)").Range("AT5:AU30") = Workbooks(TRAWN).Worksheets("BILLING FORM (NEW)").Range("AT5:AU30").Value
                Workbooks(Year(Date) & " Com Forecast (544).xlsm").Worksheets("START DATE").Range("V5") = False
                Call PASTEPREP_PASTE
            ElseIf InStr(1, WB.Name, WBN(A + 1), vbTextCompare) = False Then
                If WBNCOUNT = 2 Then
                    B = B + 1
                    GoTo SKIPWB
                End If
                If InStr(1, WB.Name, WBN(A + 2), vbTextCompare) Then
                    WB.Activate
                    WB.Worksheets("BILLING FORM (NEW)").Range("AT5:AU30") = Workbooks(TRAWN).Worksheets("BILLING FORM (NEW)").Range("AT5:AU30").Value
                    Workbooks(Year(Date) & " Com Forecast (544).xlsm").Worksheets("START DATE").Range("V5") = False
                    Call PASTEPREP_PASTE
                ElseIf InStr(1, WB.Name, WBN(A + 2), vbTextCompare) = False Then
                    B = B + 1
                End If
            End If
        End If
SKIPWB:
Next WB


If B = WBC Then
    Workbooks(Year(Date) & " Com Forecast (544).xlsm").Worksheets("START DATE").Range("V5") = True
    MsgBox "No similar workbooks found.  Run manually."
    ActiveWorkbook.Worksheets("BILLING FORM (NEW)").Range("AT5:AU30").Copy
    Exit Sub
End If

 

[DanteAmor]

If it works for you then it is correct!