Excel VBA比较两个工作簿 [英] Excel VBA comparing two workbooks

问题描述

我试图创建一个宏来比较两个Excel文件。两个Excel文件唯一的列是 eRequest ID 。目标是显示在这两个文件中没有 eRequest ID 的任何记录。

I'm trying to create a macro which compares two Excel files. The only column both Excel files have in common is "eRequest ID". The goal is to display any records that DO NOT have an "eRequest ID" in both files.

例如，如果记录1仅在两个文件之一中找到，则必须显示。不显示记录的唯一情况是如果在两个文件中都找到 eRequest ID 。

For example, if record 1 is only found in one of the two files, it has to be displayed. The only situation where records are not displayed is if the "eRequest ID" is found in both files.

在旁注中，我记录了一个简单的宏来过滤掉一些字段...我必须将这部分添加到最终的宏中。 p>

On a side note.. I recorded a simple macro to filter out some fields... I have to add in this part into the final macro as well.

ActiveSheet.ListObjects("Table_JULY15Release_Master_Inventory__2").Range. _
    AutoFilter Field:=2, Criteria1:=Array("90 BIZ - Deferred", _
    "91 GTO - Deferred", "92 BIZ - Dropped", "94 GTO - Duplicate"), Operator:= _
    xlFilterValues
ActiveSheet.ListObjects("Table_JULY15Release_Master_Inventory__2").Range. _
    AutoFilter Field:=4, Criteria1:="Core Banking"

推荐答案

假设源工作簿是打开的，listobjects在第一个工作表上。调整工作簿名称和工作表索引/名称以适应：

Assumes source workbooks are open and listobjects are on the first sheet. Adjust workbook names and sheet indexes/names to suit:

Sub Tester()
Dim lst1 As ListObject, lst2 As ListObject
Dim c1 As ListColumn, c2 As ListColumn
Dim rngDest As Range

    Set lst1 = Workbooks("WkBk A.xlsx").Sheets(1).ListObjects(1)
    Set lst2 = Workbooks("WkBk B.xlsx").Sheets(1).ListObjects(1)

    Set c1 = lst1.ListColumns("eRequest ID")
    Set c2 = lst2.ListColumns("eRequest ID")

    Set rngDest = ThisWorkbook.Sheets(1).Range("A2")

    CopyIfNotMatched c1, c2, rngDest
    CopyIfNotMatched c2, c1, rngDest

End Sub

Sub CopyIfNotMatched(c1 As ListColumn, c2 As ListColumn, rngDest As Range)
    Dim c As Range, f As Range

    For Each c In c1.DataBodyRange.Cells
        Set f = c2.DataBodyRange.Find(c.Value, , xlValues, xlWhole)
        If f Is Nothing Then
            Application.Intersect(c.EntireRow, _
                    c1.Parent.DataBodyRange).Copy rngDest
            Set rngDest = rngDest.Offset(1, 0)
        End If
    Next c
End Sub

这篇关于Excel VBA比较两个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！