VBA Macro workbook.open或workbook.activate通过变量引用 [英] VBA Macro workbook.open or workbook.activate through variable reference

问题描述

如何引用我的主要工作簿和我通过这个子程序打开的第二个工作簿？我尝试使用工作簿（client_path）。激活作为我的目标，这个宏是打开一个单独的工作簿，分配给变量 client_path 并协调列A：A中的每个（1到200）值与我的主工作簿的列K：K的所有值。如果在 client_path 工作簿（再次列A：A）找到一个值，但不在我的主工作簿上（再次列K：K） - 我想添加我的主要工作簿的M：M列的唯一值。相反的逻辑，我想要我的主要工作簿上找到的任何值，但在我的 client_path 工作簿中找不到我的主工作簿的N：N列。

我正在开发此代码的主要工作簿的名称是标题客户端DIRTY watchlist工作簿的内容 client_path 每日更新并不是随着时间的推移而变得无用。

我需要创建一个函数来完成这个变量工作簿的引用吗？

<$作为工作簿
Dim Client_client_email作为工作簿
设置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作为范围

'对于每个n在recon_list中：
Dim i As Variant
对于i = 1至200

设置email_range = ActiveWorkbook.ActiveSheet.Range（A& i）
Dim b As Variant

对于每个b在email_range

设置watchlist_range =表（客户端DIRTY监视列表）。范围（B：B ）


'如果b
下一个b

下一个i

End Sub

解决方案

您可以提前参考您的工作簿吗？

  Dim wb as workbook 
 Dim wbDirty as workbook 
 
 set wb = thisWorkbook 
 set wbDirty = workbooks.open Client_Path 
  

然后当您定义范围时，Excel会知道他们属于哪个工作簿。

  Dim rngReconcile as range 
 Dim rngWatch as range 
 
 set rngReconcile = wb.Sheets（1）.Range K：K）
 set rngWatch = wbDirty.Sheets（Client DIRTY watchlist）。范围（B：B）
  

然后继续执行循环代码

How do I reference my primary workbook and the second workbook I open through this sub procedure? I attempt to do workbooks.("client_path").activate as my goal with this macro is to open a separate workbook, which is assigned to variable client_path and 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_path workbook (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_path workbook to appear in column N:N of my primary workbook.

The name of my the primary workbook which I am developing this code is title "Client DIRTY watchlist" The contents of workbook client_path update daily and are useless as time passes.

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

解决方案

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.

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

这篇关于VBA Macro workbook.open或workbook.activate通过变量引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！