VBA 宏 workbook.open 或 workbook.activate 通过变量引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26415179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Macro workbook.open or workbook.activate through variable reference
提问by phillipsK
How do I reference my primary workbook and the second workbook I open through this sub procedure? I attempt to do workbooks.("client_path").activateas my goal with this macro is to open a separate workbook, which is assigned to variable client_pathand reconcile every (1 to 200) values in column A:A with all the values of column K:K of my primary workbook. If a value is found on the client_pathworkbook (again column A:A), but not on my primary workbook (again column K:K) - I would like to add the unique value to column M:M of my primary workbook. Opposite logic, I would like any value found on my primary workbook but not found on my client_pathworkbook to appear in column N:N of my primary workbook.
如何引用我的主要工作簿和我通过此子过程打开的第二个工作簿？我试图workbooks.("client_path").activate用这个宏来实现我的目标是打开一个单独的工作簿，它被分配给变量client_path并将 A:A 列中的每个（1 到 200）个值与我的主要工作簿的 K:K 列的所有值进行协调。如果在client_path工作簿（再次列 A:A）中找到一个值，但在我的主工作簿（再次列 K:K）上找不到值- 我想将唯一值添加到我的主工作簿的列 M:M。相反的逻辑，我希望在我的主要工作簿上找到但在我的client_path工作簿上找不到的任何值出现在我的主要工作簿的 N:N 列中。
The name of my the primary workbook which I am developing this code is title "Client DIRTY watchlist" The contents of workbook client_pathupdate daily and are useless as time passes.
我正在开发此代码的主要工作簿的名称是标题“Client DIRTY watchlist”工作簿的内容client_path每天更新，随着时间的流逝而无用。
Do I need to create a function to accomplish this variable workbook reference?
我是否需要创建一个函数来完成这个变量工作簿引用？
Sub Client_Dirty_Recon()
Dim Client_path As String
Dim Client_watchlist As Workbook
Dim Client_client_email As Workbook
Set Client_watchlist = ActiveWorkbook
Dim email_range As Range
Dim watchlist_range As Range
Application.ScreenUpdatClient = False
Client_path = Range("Path")
Workbooks.Open Client_path
Dim recon_list As Range
'For Each n In recon_list:
Dim i As Variant
For i = 1 To 200
Set email_range = ActiveWorkbook.ActiveSheet.Range("A" & i)
Dim b As Variant
For Each b In email_range
Set watchlist_range = Sheets("Client DIRTY watchlist").Range("B:B")
'if b
Next b
Next i
End Sub
回答by MMerry
Can you just make references to your workbook earlier?
你能早点参考你的工作簿吗？
Dim wb as workbook
Dim wbDirty as workbook
set wb = thisWorkbook
set wbDirty = workbooks.open Client_Path
Then when you define the ranges, Excel knows which workbook they belong to.
然后当您定义范围时，Excel 知道它们属于哪个工作簿。
Dim rngReconcile as range
Dim rngWatch as range
set rngReconcile = wb.Sheets(1).Range("K:K")
set rngWatch = wbDirty.Sheets("Client DIRTY watchlist").Range("B:B")
Then continue on with your looping code
然后继续你的循环代码
回答by Patrick Lepelletier
dim Wb as workbook
set wb= Workbooks.Open (Client_path).activate
this opens, and activates it all in one line, an sets a variable for later use (wb).
这将打开，并在一行中激活它，并设置一个变量供以后使用（wb）。
note that refering later to wb will NOT open it again, and NOT activate it again, its just reference to the wb ! (unless you tell him to)
请注意，稍后引用 wb 不会再次打开它，也不会再次激活它，它只是引用 wb ！（除非你告诉他）
