Compare 2 workbooks

[Steffmeister]

Hi everybody,

Thirst I want to say that this forum is really great, keep up the good working .

I'm not really good in macros in Excel and I've got a question if somebody have a macro which compares 2 workbooks and the difference copy in an other workbook. It's like this:

- I got a workbook called FileA.xls and a worksheet SheetA. In column C stands a lot of numbers (like ip-addresses or something).
- The other workbook called FileB.xls and a worksheet SheetB. Here stands in column D also some numbers.
- I'd like to compare workbook FileB with FileA and put the data from FileB which are NOT in FileA in an other workbook.

Is this possible? If yes, can someone plz help me?

Tnx for now,
Grts Steffmeister

 

[Andrew Poulsom]

Welcome to the Board.

Try this:

Sub Test()
    Dim WF As WorksheetFunction
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim WBNew As Workbook
    Dim r As Long
    Dim Cell As Range
    Dim i As Long
    Set WF = WorksheetFunction
    Set Sh1 = Workbooks("FileA.xls").Worksheets("SheetA")
    Set Sh2 = Workbooks("FileB.xls").Worksheets("SheetB")
    Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
    Set WBNew = Workbooks.Add
    r = 1
    For Each Cell In Rng
        On Error Resume Next
        i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
          If Err <> 0 Then
            Err.Clear
            WBNew.ActiveSheet.Cells(r, 1).Value = Cell.Value
            r = r + 1
        End If
        On Error GoTo 0
    Next Cell
End Sub

 

[Steffmeister]

WAUWWW tnx for the fast answer, this is really GREAT.
Tnx a lot, it works perfect.

Grts Steffmeister

 

[Steffmeister]

Sorry for the inconvenience but is it also possible to make a command button in a workbook and when I push the command button the comparation begins of FileA.xls and FileB.xls and put the difference in the same file with the command button?

Grts Steffmeister

 

[Andrew Poulsom]

Sorry for the inconvenience but is it also possible to make a command button in a workbook and when I push the command button the comparation begins of FileA.xls and FileB.xls and put the difference in the same file with the command button?

Grts Steffmeister

Yes. Do you want the results on an existing worksheet or on a new worksheet?

 

[Steffmeister]

On the existing worksheet plzz

 

[Andrew Poulsom]

Add a CommandButton from the Forms Toolbar and assign it this code:

Sub Test()
    Dim WF As WorksheetFunction
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim r As Long
    Dim Cell As Range
    Dim i As Long
    Set WF = WorksheetFunction
    Set Sh1 = Workbooks("FileA.xls").Worksheets("SheetA")
    Set Sh2 = Workbooks("FileB.xls").Worksheets("SheetB")
    Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
    ActiveSheet.Cells.Clear
    r = 1
    For Each Cell In Rng
        On Error Resume Next
        i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
          If Err <> 0 Then
            Err.Clear
            ActiveSheet.Cells(r, 1).Value = Cell.Value
            r = r + 1
        End If
        On Error GoTo 0
    Next Cell
End Sub

 

[Steffmeister]

It works FANTASTIC. Thank you very much

Grts Steffmeister

 

[Steffmeister]

Hi Andrew Poulsom,

I got a new question, maybe you know the answer or somebody else on this forum?

I got 2 input files, FileA.xls and FileB.xls. But this can also be various, zo FileC.xls for example. Is it possible to change the filename in the excel sheet so that he knows which file he must get?
For example in cell D6 stands C:\FileC.xls and in cell D7 C:\FileA.xls
When I change cell D6 in C:\FileB.xls he must get FileB.xls.

And I prefered to not open the excel files manually. Now I must first open the files to compare.

Second question is:
There are a lot of numbers, is it possible to put a 'Stop' button in it?

Tnx by now.

Steffmeister

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim WF As WorksheetFunction
    Dim Sh As Worksheet
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim r As Long
    Dim Cell As Range
    Dim i As Long
    Set WF = WorksheetFunction
    Set Sh = ActiveSheet
    Set Sh1 = Workbooks.Open(Sh.Range("D7").Value).Worksheets(1)
    Set Sh2 = Workbooks.Open(Sh.Range("D6").Value).Worksheets(1)
    Set Rng = Sh2.Range("D1:D" & Sh2.Range("D65536").End(xlUp).Row)
    Sh.Columns(1).Cells.Clear
    r = 1
    For Each Cell In Rng
        On Error Resume Next
        i = WF.Match(Cell.Value, Sh1.Range("C:C"), False)
          If Err <> 0 Then
            Err.Clear
            Sh.Cells(r, 1).Value = Cell.Value
            r = r + 1
        End If
        On Error GoTo 0
    Next Cell
    Sh1.Parent.Close
    Sh2.Parent.Close
End Sub

You can stop the Macro by pressing Esc or Ctrl+Break.