引用不同的工作簿

Question

我在这里使用指南:在Excel中使用宏查找和替换循环

我有一个工作簿"TLA查找。xlsx"保存在共享位置。它有一个叫做TLAs的表。A列是tla列表，B列是相应的企业名称。

我有一个宏来检查TLA查找工作簿，在TLA存在的地方，将其替换为企业名称。我可以在同一个工作簿中做到这一点。

我有不同的工作簿，我希望这个查找/替换发生。每次我都要从TLA查找工作簿中复制TLA表。我想要自动引用那个工作簿。

我如何引用TLA查找作为宏需要查找/替换文本的工作簿?

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:CLE01Team_QAUpcoming Change HighlightsTLA 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 

Answer 1

您可以很容易地做到这一点，但您必须了解ThisWorkbook和ActiveWorkbook之间的区别。(请参阅此处、此处和此处的解释以获得一些指导)

有了这些知识，只要清楚你在代码中引用的是哪个工作簿:

编辑:下面示例中的扩展代码

Option Explicit
Sub find_replace_3()
Dim tlaLookupWB As Workbook
Dim tlaSheet As Worksheet
Set tlaLookupWB = Workbooks.Open(Filename:= _
"K:CLE01Team_QAUpcoming Change HighlightsTLA 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