Reference a different workbook

Question

I am using the guidance here: Find and replace in a loop using macro in Excel

I have a workbook "TLA Lookup.xlsx" saved in a shared location. It has a sheet named TLAs. Column A consists of a list of TLAs and column B is the corresponding business name.

I have a macro to check the TLA Lookup workbook and wherever the TLA exists, replace it with the business name. I can do this within the same workbook.

I have different workbooks where I want this find/replace to happen. I have to copy the TLAs sheet from the TLA Lookups workbook over every time. I want to reference that workbook automatically instead.

How do I reference the TLA Lookup as being the workbook the macro needs to look in for the find/replace text?

Sub find_replace_2()
    Dim TLA As String
    Dim NAME As String
    Dim i As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
   
    'Open the Workbook that has all of the TLAs and CI Names from the K drive,
    ' so now both workbooks are open

    Workbooks.Open Filename:= _
      "K:\CLE01\Team_QA\Upcoming Change Highlights\TLA Lookup.xlsx"
        
    Set wb = TLA Lookup.xlsx    ' <----  Here is where I get a syntax error
    Set sht1 = wb.Sheets("TLAs")  

    For i = 1 To 4000
        TLA = wb.sht1.Range("A" & i).Value
        NAME = wb.sht1.Range("B" & i).Value

        Selection.Replace What:=TLA, replacement:=NAME _
          , LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
          :=False, ReplaceFormat:=False            
    Next i

End Sub

PeterT · Answer 1 · 2021-02-06T02:19:12.723

You can easily do this, but you have to understand the difference between ThisWorkbook and the ActiveWorkbook. (See explanations here, here, and here for some guidance.)

With that knowledge, just be clear about which workbook you're referencing in your code:

EDIT: expanded code in the example below

Option Explicit

Sub find_replace_3()
    Dim tlaLookupWB As Workbook
    Dim tlaSheet As Worksheet
    Set tlaLookupWB = Workbooks.Open(Filename:= _
        "K:\CLE01\Team_QA\Upcoming Change Highlights\TLA Lookup.xlsx")
    Set tlaSheet = tlaLookupWB.Sheets("TLAs")
    
    '--- determine how many rows of TLAs exist
    Dim numberOfTLAs As Long
    With tlaSheet
        numberOfTLAs = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    '--- replacements will be made in the currently active workbook
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    '--- now check all of the TLAs and make replacements if found
    Dim i As Long
    For i = 1 To numberOfTLAs
        Dim tla As String
        Dim name As String
        tla = tlaSheet.Cells(i, 1).Value
        name = tlaSheet.Cells(i, 2).Value
            
        '--- search all of the worksheets in the current workbook
        '    and replace the tla with the name
        Dim ws As Worksheet
        For Each ws In wb.Sheets
            ws.Cells.Replace What:=tla, Replacement:=name, _
                             LookAt:=xlWhole, SearchOrder:=xlByRows, _
                             MatchCase:=False, SearchFormat:=False, _
                             ReplaceFormat:=False
        Next ws
    Next i
    
    tlaWB.Close SaveChanges:=False
End Sub

Thank you, this certainly helped me understand the workbook piece I was missing. However now the actual replacement doesn't work. And the comment section won't let me put the new code in lol. It opens and closes the TLA Lookup workbook, but it never does the actual replacement. — Shae1999, Feb 05 '21 at 14:01
@Shae1999 - The code in the example provided above works in my own sample data. — PeterT, Feb 06 '21 at 02:19
What I see is happening is that it is making the change, but on the lookup sheet instead of the sheet I'm working in. I think I am still having trouble with ActiveWorkbook vs ThisWorkbook. — Shae1999, Feb 08 '21 at 17:43
The tlaLookup workbook should never be edited. I am in "book1" and I have a cell highlighted and I want the macro to reference the tlaLookup workbook, column A for the TLA that exists in Book1 cell a1, and replace it with the contents of column B for the matching row. So "ABC" in book1 now becomes "Alpha Bravo Charlie" because tlaLookup shows ABC in cell a4 and Alpha Bravo Charlie in b4. — Shae1999, Feb 08 '21 at 17:56
Please modify the code example to match your data, workbooks, and environment. — PeterT, Feb 08 '21 at 22:15

Reference a different workbook

1 Answers1