Q:

Consolidate two Excel Workbooks based on multiple criteria

I want to consolidate two excel files, both with a different layout. The only common columns between the two files are "name" and "postal code".
File 1 is the "basefile", file 2 contains the additional information I want to integrate into file 1.
The additional information (address, country, field7 and field8) should only be copied into the basefile when both name and postal code of the corresponding rows are the same. The basefile has multiple worksheets, the corresponding row could be in any of the sheets.
Both excel files are quite large (>60000 rows, 5 columns). The code is supposed to take the first entry from file 2 and search for the corresponding entry in file 1. If found, the additional information will be copied into file 1. Then (or if no corresponding entry found) the procedure is rerun, this time with the second entry from file 2 - as long as all entry from file 2 have been consolidated into file 1.
The below code works, but is way too slow. It took about two minutes to consolidate one single row.
Do you have any suggestions on how to improve the performance?
Sub merging_two_excel_files()
'

Dim data_path As String
Dim filename_base As String
Dim filename_addon As String

Dim xlApp As Excel.Application
Dim xlBook_base As Workbook
Dim xlBook_addon As Workbook

data_path = "..."
filename_base = "file1"
filename_addon = "file2"
Set xlApp = CreateObject("Excel.Application")
Set xlBook_base = xlApp.Workbooks.Open(data_path & filename_base)
Set xlBook_addon = xlApp.Workbooks.Open(data_path & filename_addon)

screenUpdateState = xlApp.ScreenUpdating
statusBarState = xlApp.DisplayStatusBar
calcState = xlApp.Calculation
eventsState = xlApp.EnableEvents
xlApp.ScreenUpdating = False
xlApp.DisplayStatusBar = False
xlApp.Calculation = xlCalculationManual
xlApp.EnableEvents = False

With xlBook_addon.Worksheets(1)
Dim number_of_rows_addon As Long
number_of_rows_addon = .Range("B1", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count
End With

For k = 2 To number_of_rows_addon Step 1
Dim name_addon As String
Dim postalcode_addon As String
Dim address_addon As String
Dim country_addon As String
Dim field7_addon As String
Dim field8_addon As String

name_addon = xlBook_addon.Worksheets(1).Cells(k, 2).Value
postalcode_addon = xlBook_addon.Worksheets(1).Cells(k, 4).Value
address_addon = xlBook_addon.Worksheets(1).Cells(k, 3).Value
country_addon = xlBook_addon.Worksheets(1).Cells(k, 6).Value
field7_addon = xlBook_addon.Worksheets(1).Cells(k, 7).Value
field8_addon = xlBook_addon.Worksheets(1).Cells(k, 8).Value

Dim number_of_worksheets_base As Long
number_of_worksheets_base = xlBook_base.Worksheets.Count
For d = 1 To number_of_worksheets_base Step 1

With xlBook_base.Worksheets(d)
Dim number_of_rows_base As Long
number_of_rows_base = .Range("B1", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count

For c = 2 To number_of_rows_base Step 1
If name_addon = .Cells(c, 6).Value And postalcode_addon = .Cells(c, 1).Value Then
.Cells(c, 7).Value = address_addon
.Cells(c, 8).Value = country_addon
.Cells(c, 9).Value = field7_addon
.Cells(c, 10).Value = field8_addon
Else
End If

Next c
End With

Next d

Next k

xlApp.ScreenUpdating = screenUpdateState
xlApp.DisplayStatusBar = statusBarState
xlApp.Calculation = calcState
xlApp.EnableEvents = eventsState

Application.DisplayAlerts = False
xlBook_base.Close SaveChanges:=True
Application.DisplayAlerts = True
xlBook_addon.Close SaveChanges:=False

xlApp.Application.Quit
Set xlApp = Nothing

MsgBox "Done!"

End Sub

A:

The fact you're creating a whole new instance of Excel to do this in is going to slow you down a lot - every one of those calls to the second instance has to be passed between the two processes (the new instance and the one where your code is running) - that takes a lot of overhead as the test method below shows:
Sub TEST()

    Dim xlapp As Excel.Application, wb As Excel.Workbook
    Dim c As Range, v, r As Long, t

    Set xlapp = CreateObject("excel.application")
    xlapp.Visible = True

    'using another Excel instance
    t = Timer
    Set wb = xlapp.Workbooks.Add()
    For r = 1 To 10000
        v = wb.Sheets(1).Cells(r, 1).Value
    Next r
    Debug.Print Timer - t '~ 20secs <<<<<<<<<

    xlapp.Quit

    'using the current instance
    t = Timer
    Set wb = ThisWorkbook
    For r = 1 To 10000
        v = wb.Sheets(1).Cells(r, 1).Value
    Next r
    Debug.Print Timer - t '~0.08 secs <<<<<<<<

End Sub

Using a second instance is much slower.
Without the second Excel instance, and exiting the loop as soon as a match is found:
Sub merging_two_excel_files()

    Const data_path As String = "..."
    Const filename_base As String = "file1"
    Const filename_addon As String = "file2"

    Dim xlBook_base As Workbook
    Dim xlBook_addon As Workbook, shtAddon As Worksheet
    Dim last_row_addon As Long, name_addon As String
    Dim postalcode_addon As String, shtBase As Worksheet
    Dim last_row_base As Long, k As Long, c As Long, rw As Range

    Set xlBook_base = Workbooks.Open(data_path & filename_base)
    Set xlBook_addon = Workbooks.Open(data_path & filename_addon)
    Set shtAddon = xlBook_addon.Worksheets(1)

    last_row_addon = shtAddon.Cells(shtAddon.Rows.Count, 2).End(xlUp).Row

    For k = 2 To last_row_addon

        Set rw = shtAddon.Rows(k)

        name_addon = rw.Cells(2).Value
        postalcode_addon = rw.Cells(4).Value

        For Each shtBase In xlBook_base.Worksheets

            With shtBase

                last_row_base = .Cells(.Rows.Count, 2).End(xlUp).Row

                For c = 2 To last_row_base
                    If name_addon = .Cells(c, 6).Value And _
                       postalcode_addon = .Cells(c, 1).Value Then

                        .Cells(c, 7).Value = rw.Cells(3).Value
                        .Cells(c, 8).Value = rw.Cells(6).Value
                        .Cells(c, 9).Value = rw.Cells(7).Value
                        .Cells(c, 10).Value = rw.Cells(8).Value
                        GoTo found '### exit loop after finding the matching row....
                    End If
                Next c

            End With

        Next shtBase
found:

    Next k

    Application.DisplayAlerts = False
    xlBook_base.Close SaveChanges:=True
    Application.DisplayAlerts = True
    xlBook_addon.Close SaveChanges:=False

    MsgBox "Done!"

End Sub

Compiles but untested.

