使用 VBA 比较两个 Excel 工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26254674/
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
Using VBA to compare two excel workbooks
提问by dekio
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 postwhich 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.
我正在关注这篇关于比较两个工作簿的帖子。因为我想比较两个内容相同的 excel 文件，所以我做了一个非常相似的代码。但是，我的代码似乎没有比较这两个文件，而是比较文件 A 与文件 A，或文件 B 与文件 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
代码所做的是获取两个工作簿，获取名为资产负债表的工作表，然后比较资产负债表在两个工作簿中是否具有相同的值。所以我们不必遍历所有单元格，工作表被加载到一个 Variant 数组中。您可以查看资产负债表的图片只是为了有一个想法：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?
另外，我发现了一个新问题，我是否可以浏览所有工作表而无需提供特定名称？在我的代码中，我必须插入“资产负债表”作为工作表名称，但如果我有多个工作表怎么办？即使通过循环，有没有人对此有一个好主意，这不会使我的 excel 内存不足或变得太慢？
回答by marg
You are using workbookA so set sheetB.
您正在使用工作簿A，因此设置工作表B。
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("..."))
Worksheets 函数的直接调用将始终引用 ActiveWorkbook。而是始终从相关对象调用它（wkbA.Worksheets("...") 或 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)
