Data Consolidation In Excel - Part 1

Note: We are not going to discuss here the Consolidation function of Excel. The said function is used if you want to sum up multiple numbers from different Excel files that have same format into a single Excel file. I've got limited time to post to discuss it but will try to tackle it in the near future.

In this post, we will combine records from different files with different format. Appending them all in a single Excel file. No summation will be done.


Data Consolidation

Is it to much tedious if you copy and paste manually sets of data from multiple files and append them all together in a master file? Let's say you have 50 files of records and you open them one by one and copy the records and paste into a single file. And the most boring of all about it is that you need to do it over and over again in a weekly basis. Of course, you will agree with me that it is tedious.

If you find yourself in that situation and don't want to do the same thing manually weekly, then you can automate the whole process. The long hours of doing it manually can be reduced in an hour or two with a macro program.


Source Data

So let's start by creating the files to be combined together. For our example, we will create 3 different source files; an Excel file, CSV file, and a Tab delimited file.

Excel File -- Create a new Excel file and copy the data in the following screen shot and save as Excel file, of course :)














CSV File -- In Excel, copy the data in the following screen shot and save it in CSV format by clicking "Save As" in the File menu. Change the file type to CSV before clicking the Save button.












Tab Delimited -- In Excel, copy the data in the following screen shot and save it in Tab delimited text file by clicking the "Save As" in the File menu. Change the file type to "Tab delimited" before clicking the Save button.












Recording Data Import

AT this point, we will extract records from the files that we created while we are macro recording it. To start the Macro Recorder, please see my post on how to do that at Easiest Way to Learn Excel VBA Programming by Macro Recorder. Once it is up and running, we will do the data extraction of the 3 files. But it is much better if you will start and stop the macro recorder for each file extraction so that each file will have its own procedure module. then we will examine each module one by one to learn the difference.

To extract the file, follow the instruction in my previous post, Export To Excel.

You should generate the same codes as below:

Source Code for Excel file.

Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 9/14/2008 by Joel.Protusada
    '
    
    '
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password="""";User ID=Admin;Data Source=" & _
        "E:\Consolidation\Sales branch1.xls;" & _
        "Mode=Share Deny Write;", _
        "Extended Properties=""HDR=YES;"";Jet OLEDB:" & _
        "System database="""";" & _
        "Jet OLEDB:Registry Path="""";Jet OLEDB:" & _
        "Database Password="""";Jet OLEDB:", _
        "Engine Type=35;Jet OLEDB:Database " & _
        "Locking Mode=0;Jet OLEDB:" & _
        "Global Partial Bulk Ops=2;Jet OLEDB:Global " & _
        "Bulk Transactions=1;Jet OL", _
        "EDB:New Database Password="""";Jet OLEDB:" & _
        "Create System Database=" & _
        "False;Jet OLEDB:Encrypt Database=False;Jet " & _
        "OLEDB:Don't Copy Locale", _
        " on Compact=False;Jet OLEDB:Compact " & _
        "Without Replica " & _
        "Repair=False;Jet OLEDB:SFP=False" _
        ), Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array("Sheet1$")
        .Name = "Sales branch1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "E:\Consolidation\Sales branch1.xls"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Source code for CSV file.

Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 9/14/2008 by Joel.Protusada
    '
  
    '
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\Consolidation\Sales branch2.csv", _
        Destination:=Range("A1"))
        .Name = "Sales branch2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Sheet3").Select
End Sub

Source code for the tab delimited file.

Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 9/14/2008 by JPCORP
    '
  
    '
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\Consolidation\Sales branch3.txt", _
        Destination:=Range("A1"))
        .Name = "Sales branch3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

As you can see there are almost no difference between the CSV and Tab file. So in our programming we will modify the codes above and we will create a single subroutine to handle both CSV and Tab files, and a separate subroutine for Excel files.


Project Details

Before we start with the actual programming, we need to be precised in the parameters of our consolidation. Below is the brief specifications of it:

• To trigger the consolidation, an option in the menu should be added to run it. To do that visit my post Create Customize Excel Menu.
  
• All the files to be consolidated should be residing in the same folder as the master file. The master file is an Excel file that will serve as the repository of all the records. It also has the consolidation macro program.
• There will be only three data file formats to be recognized by the program; Excel, CSV, and tab delimited. If there is a need for other format, you can easily modify the program that we will make here.
• program should follow the flow chart below:

Modified Macro Program

As I've said we will modify the code above to subroutines that we can use and reuse in our programming. Below is the modified recorded macro for the Excel file extraction:

Sub ExtractExcelData(strPath As String, _
strFile As String, strSheet As String)
Sheets(strSheet).Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
 "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Password="""";User ID=Admin;Data Source=" & _
 strPath & strFile & ";Mode=Share Deny Write;", _
 "Extended Properties=""HDR=YES;"";Jet OLEDB" & _
 ":System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB" & _
 ":Database Password="""";Jet OLEDB:", _
 "Engine Type=35;Jet OLEDB:Database Locking Mode=0;" & _
 "Jet OLEDB:Global Partial Bulk Ops=2;" & _
 "Jet OLEDB:Global Bulk Transactions=1;Jet OL", _
 "EDB:New Database Password="""";Jet OLEDB:" & _
 "Create System Database=False;Jet OLEDB:" & _
 "Encrypt Database=False;Jet OLEDB:Don't Copy Locale", _
 " on Compact=False;Jet OLEDB:Compact Without Replica " & _
 "Repair=False;Jet OLEDB:SFP=False" _
 ), Destination:=Range("A1"))
 .CommandType = xlCmdTable
 .CommandText = Array("Sheet1$")
 .Name = "Sales branch1"
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .BackgroundQuery = True
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .PreserveColumnInfo = True
 .SourceDataFile = strPath & strFile
 .Refresh BackgroundQuery:=False
End With
End Sub

To test the above procedure, the correct parameters should be supplied like the example below:

Sub TestExtractExcelData()
ExtractExcelData "E:\consolidation\", "Sales branch1.xls", "sheet4"
'Parameters are:
'strPath - folder where to get the file
    'strFile - Excel filename of the source data.
    'strSheet - woeksheet where to put the data.
End Sub

Here's the subroutine for the CSV and Tab files extraction:

Sub ExtractTextData(strPath As String, _
strFile As String, strSheet As String, isCSV As Boolean)
Dim MyRecords
Sheets(strSheet).Select
If isCSV Then
   Set MyRecords = ActiveSheet.QueryTables.Add(Connection:= _
   "TEXT;" & strPath & strFile, Destination:=Range("A1"))
Else
   Set MyRecords = ActiveSheet.QueryTables.Add(Connection:= _
   "TEXT;" & strPath & strFile, Destination:=Range("A1"))
End If
With MyRecords
   .Name = Left(strFile, Len(strFile) - 4)
   If isCSV Then
       .TextFileCommaDelimiter = True
       .TextFileTabDelimiter = False
   Else
       .TextFileCommaDelimiter = False
       .TextFileTabDelimiter = True
   End If
   .FieldNames = True
   .RowNumbers = False
   .FillAdjacentFormulas = False
   .PreserveFormatting = True
   .RefreshOnFileOpen = False
   .RefreshStyle = xlInsertDeleteCells
   .SavePassword = False
   .SaveData = True
   .AdjustColumnWidth = True
   .RefreshPeriod = 0
   .TextFilePromptOnRefresh = False
   .TextFilePlatform = 437
   .TextFileStartRow = 1
   .TextFileParseType = xlDelimited
   .TextFileTextQualifier = xlTextQualifierDoubleQuote
   .TextFileConsecutiveDelimiter = False
   .TextFileSemicolonDelimiter = False
   .TextFileSpaceDelimiter = False
   .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
   .TextFileTrailingMinusNumbers = True
   .Refresh BackgroundQuery:=False
End With
End Sub

Here's the code to test it.

Sub TestExtractCSVData()
ExtractTextData "E:\consolidation\", _
"Sales branch2.csv", "sheet5", True
End Sub

Sub TestExtractTabData()
ExtractTextData "E:\consolidation\", "Sales branch3.txt", _
"sheet6", False
End Sub
'Parameters are the same as in the Excel routine.
'The fourth parameter is a boolean type which
'means that if the TRUE, it is a CSV file,
'otherwise Tab file. 

So there you go. Those are the codes to do it. I will post soon the whole VBA program as it is too long to include it here.

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.