使用VBA比较两个excel工作簿 [英] Using VBA to compare two excel workbooks

问题描述

是的，我知道还有一些与此相关的其他话题，但是我认为如果我创造另一个题目会更好，因为这是一个不同的。无论如何，如果有人认为我没有遵守论坛规则，请做你所要做的。

我正在关注这个 post 正在谈论比较两个工作簿。因为我想比较两个excel文件与相同的内容，我做了一个非常相似的代码。但是，我的代码似乎没有比较两个文件，而是将文件A与文件A进行比较，或者将文件B与文件B进行比较。

代码是获取两个工作簿，获取名为资产负债表的工作表，然后比较两个工作簿中的资产负债表是否具有相同的值。所以我们不必循环遍历所有单元格，这些工作表被加载到Variant数组中。您可以看到资产负债表的图片只是一个想法：
http：/ /i.stack.imgur.com/tc8Nr.png

我的代码是这样的：

  Sub CompareWorkbooks（）
 
 Dim varSheetA As Variant 
 Dim varSheetB As Variant 
 Dim strRangeToCheck As String 
 Dim iRow As Long 
 Dim iCol As Long 
 
 nlin = 1 
 ncol = 1 
 
'从工作簿中获取工作表
设置wbkA =工作簿.Open（文件名：=C：\Users\Desktop\BalanceSheet.xls）
设置varSheetA = wbkA.Worksheets（资产负债表）或您需要的任何表格
 
设置wbkB = Workbooks.Open（文件名：=C：\Users\Desktop\BalanceSheet_old.xls）
设置varSheetB = wbkB.Worksheets（资产负债表）或您需要的任何表格
 
 strRangeToCheck =B6：D49
'如果您知道数据只能在较小的范围内，请减小范围的大小波夫。 
 Debug.Print now 
 varSheetA = Worksheets（资产负债表）。范围（strRangeToCheck）
 varSheetB =工作表（资产负债表）。范围（strRangeToCheck）是。 
 Debug.Print now 
 
对于iRow = LBound（varSheetA，1）到UBound（varSheetA，1）
对于iCol = LBound（varSheetA，2）To UBound（varSheetA， 2）
如果varSheetA（iRow，iCol）= varSheetB（iRow，iCol）然后
'单元格是相同的。 
'什么都不做
 Else 
'单元格是不同的。让我们用信息
 Windows（MainTemplate.xlsm）填充我们的主模板。激活
单元格（nlin，ncol）= varSheetA（iRow，2）'提供更改字段的名称
单元格（nlin，ncol + 1）= varSheetA（iRow，iCol）'给我工作簿中的值
单元格（nlin，ncol + 2）= varSheetB（iRow，iCol）'给我工作簿B中的值
细胞（nlin，ncol + 3）= nlin'给我行位置
细胞（nlin，ncol + 4）= ncol'给我列位置
 nlin = nlin + 1 
结束如果
下一个
下一个
 
 End Sub 
  

任何人都可以猜测错误在哪里？为什么我没有得到不同的单元格？

另外，我发现一个新的问题，我可以通过所有的表单运行，而不必给出具体的名称？在我的代码中，我必须插入资产负债表作为工作表名称，但如果我有几个工作表怎么办？即使循环，有没有人有一个好主意，这不会使我的excel用尽内存或太慢？

解决方案

您正在使用工作簿A设置sheetB。

 设置varSheetB = wbkA.Worksheets（资产负债表）'或者你需要的任何表格
  

应该是：

 设置varSheetB = wbkB.Worksheets（资产负债表）或您需要的任何表格
  

对不起，我第一次错过了这个：

  varSheetA =工作表（资产负债表）。范围（strRangeToCheck）
 varSheetB =工作表（资产负债表）。范围（strRangeToCheck）
  

应该是：

  varSheetA = varSheetA.Range（strRangeToCheck）
 varSheetB = varSheetB.Range（strRangeToCheck）
  

Worksheets函数的直接调用将始终引用ActiveWo rkbook。
相反，总是从相关对象（wkbA.Worksheets（...）或wkbB.Worksheets（...）中调用它）

顺便说一句。您也可以将这两个作业组合成一个：

  strRangeToCheck =B6：D49
设置wbkA = Workbooks.Open （文件名：=C：\Users\Desktop\BalanceSheet.xls）
设置varSheetA = wbkA.Worksheets（资产负债表）。范围（strRangeToCheck）
设置wbkB =工作簿。打开（文件名：=C：\Users\Desktop\BalanceSheet_old.xls）
设置varSheetB = wbkB.Worksheets（资产负债表）。范围（strRangeToCheck）
  

Yeah, I know there are some other topics related to that, but I thought it would be better if I create another one, since this is kind of different. Anyway, if someone believe I didn't follow the forum rules, please do what you have to do.

I was following this post which is talking about comparing two workbooks. As I want to compare two excel files with the same content I made a pretty similar code. However, my code doesn't seem to be comparing the two files, instead it is comparing the file A with file A, or file B with file B.

What the code does is to get two Workbooks, get the worksheet named Balance Sheet and then compare if the Balance Sheets have the same values in both workbooks. So we don't have to loop through all cells, the sheets are loaded into a Variant array. You can see a picture of the balance sheet just to have an idea: http://i.stack.imgur.com/tc8Nr.png

My code is this one:

Sub CompareWorkbooks()

Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long

nlin = 1
ncol = 1

'Get the worksheets from the workbooks
Set wbkA = Workbooks.Open(Filename:="C:\Users\Desktop\BalanceSheet.xls")
Set varSheetA = wbkA.Worksheets("Balance sheet") ' or whatever sheet you need

Set wbkB = Workbooks.Open(Filename:="C:\Users\Desktop\BalanceSheet_old.xls")
Set varSheetB = wbkB.Worksheets("Balance sheet") ' or whatever sheet you need

strRangeToCheck = "B6:D49"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Balance Sheet").Range(strRangeToCheck)
varSheetB = Worksheets("Balance Sheet").Range(strRangeToCheck) ' or whatever your other sheet is.
Debug.Print Now

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
        If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
            ' Cells are identical.
            ' Do nothing.
        Else
           ' Cells are different. Let's fill our main template with the information
            Windows(MainTemplate.xlsm).Activate
            Cells(nlin, ncol) = varSheetA(iRow, 2) 'Gives the name of the changed field
            Cells(nlin, ncol + 1) = varSheetA(iRow, iCol) 'Gives me the value in workbookA
            Cells(nlin, ncol + 2) = varSheetB(iRow, iCol) 'Gives me the value in workbookB
            Cells(nlin, ncol + 3) = nlin 'Gives me the row location
            Cells(nlin, ncol + 4) = ncol 'Gives me the column location
            nlin = nlin + 1
        End If
    Next
Next

End Sub

Can anyone take a guess of where the error is? Why am I not getting the cells that are different?

Also, I found a new problem, is it possible for me to run through all sheets without having to give a specific name? In my code I have to insert "Balance Sheet" as the sheet name, but what if I have several worksheets? Even by making a loop, does anyone have a good idea for this which won't make my excel run out of memory or get too slow?

解决方案

You are using workbookA so set sheetB.

Set varSheetB = wbkA.Worksheets("Balance sheet") ' or whatever sheet you need

should be:

Set varSheetB = wbkB.Worksheets("Balance sheet") ' or whatever sheet you need

Sorry I missed this the first time around:

varSheetA = Worksheets("Balance Sheet").Range(strRangeToCheck)
varSheetB = Worksheets("Balance Sheet").Range(strRangeToCheck)

should be:

varSheetA = varSheetA.Range(strRangeToCheck)
varSheetB = varSheetB.Range(strRangeToCheck)

Direct calls of the Worksheets function will always refer to the ActiveWorkbook. Instead always call it from the relevant object (wkbA.Worksheets("...") or wkbB.Worksheets("..."))

Btw. you can also combine both assignments into one:

strRangeToCheck = "B6:D49"
Set wbkA = Workbooks.Open(Filename:="C:\Users\Desktop\BalanceSheet.xls")
Set varSheetA = wbkA.Worksheets("Balance sheet").Range(strRangeToCheck)
Set wbkB = Workbooks.Open(Filename:="C:\Users\Desktop\BalanceSheet_old.xls")
Set varSheetB = wbkB.Worksheets("Balance sheet").Range(strRangeToCheck)

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