CODEWITHSUNDEEP
vbaexcel
dekio
dekio

Reputation: 989

Using VBA to compare two excel workbooks

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: https://i.sstatic.net/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?

Upvotes: 2

Views: 33973

Answers (1)

marg
marg

Reputation: 2827

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)

Upvotes: 2

Related Questions