COMBINE MULTIPLE EXCEL FILES WITH BOTH MATCHING AND DIFFERING FIELDS IN ONE MASTER WORKBOOK

See this how to video posted to my YouTube channel, and the below instructions taken from the video.

Collect all of your Excel files in a single folder. In this sample set we can see that while some of the columns in the spreadsheets have the same headings, many are unique to the individual Excel files.

Create a new Access database. Enter Visual Basic by pressing ALT + F11

We'll be using three macros which I have already inserted in separate modules in Visual Basic. To create a new module just right click on the left on the Modules folder and select Insert . . . Module

The first module will import the source Excel files. The vba code was created by Ken Snell and is available on the Tip of the Night for October 5, 2017. See www.litigationsupporttipofthenight.com Be sure to set the line beginning blnHasFieldNames = to True not False, to import the first row of the Excel files as the column headings.

Enter the path to the folder containing the Excel files on the line beginning: strPath =

Press the play button and the vba code should import the Excel files as separate tables into the Access database.

Now we want to get a list of all of the fields in each of table. Go back to visual basic and use the vba code I have inserted in the second module, which was created by Ken Puls and was posted as the Tip of the Night for October 6, 2017.

It should not be necessary to edit this vba code. Press play and an Excel spreadsheet will be generated which lists the table names in column A and the field names in column B.

Now we'll de-dupe the field names and use the transpose paste option in Excel to put them in the first row of a worksheet.

Note that the vba code we just used to generate the table field names has a flaw. It does not pick up the names of the first field in each table. In this example only the first field in the Parks table is omitted, and there is overlap between the tables which accounts for the first field for the other tables.

Add the missing field names to the worksheet.

In the second row we want each cell to have more than 255 characters. This will prompt Access to make each field a Long Text field when the field names are imported.

Use the LEN formula in Excel to confirm you have the right number of characters. Then select cell A2, then select the cells to the right and press CTRL + R to copy the data to all of the other cells on the second row.

Next we'll go to the External Data tab of the Access ribbon and select New Data Source . . . From File . . .Excel to import the fields into a new table.

In the Import Wizard be sure to check off the box making the data in the first row the column headings of the Access table.

The third and final vba code will insert the source tables one by one into the new master table. This code is available in the MS Office Dev Center and was posted as the Tip of the Night for October 8, 2017

First we need to enter the path to the Access database we're working in.

On the line containing the SQL command INSERT INTO enter the name of the master table. Then put the name of the source table following FROM in parentheses. It's necessary to run this macro once for each source table.

The data has been added for the first source table - Batting.

As you can see, where the tables had a field in common, the data from all sources is now in a single field.

Add All Records in One Access Table to Another Table that Contains the Same Fields and Others

Tonight's tip concerns VBA code posted to the Dev Center for MS Office at https://msdn.microsoft.com/en-us/library/office/ff834799.aspx . The VBA code runs a SQL INSERT INTO command to insert all of the records in one Access table into another table that must contain all of the same fields, but may contain many other fields. This is particularly helpful when you're trying to merge multiple tables into a master table and there are numerous fields - some of which match, and other that don't.

In this example, we have two tables. All of the fields in the table named, 'Batting' are in the table named, 'BattingPost' which also contains one extra field named, 'Round'.

In a new module in Visual Basic we insert this code listing the path to our database on the line beginning 'Set dbs', and list the table records will be added to after INSERT INTO and the source of the records after FROM.

Option Compare Database

Sub InsertIntoX1() Dim dbs As Database ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("C:\Users\SeanKOShea\Documents\Database8.accdb") ' Select all records in the New Customers table ' and add them to the Customers table. dbs.Execute " INSERT INTO BattingPost " _ & "SELECT * " _ & "FROM [Batting];" dbs.Close End Sub

As you can see we end up with this result - a table containing 19592 records - the sum of the 7902 records in the Batting table and the 11690 records in the BattingPost table. The data from Batting goes into the BattingPost field where the names are the same.

Access VBA Code to Generate List of All Fields in All Tables

Ken Puls has posted the below Visual Basic code for MS Access which will generate an Excel file that shows all of the fields in all of the tables in database.

So when you have a set of tables like these:

. . . you can press ALT + F11 to enter Visual Basic. Right click on Modules in the list to the left and insert a new module. Paste in the code and press play.

An Excel file like this will be generated listing the tables in one column and the fields in each table in the second column. Note however that the code has a flaw in it: it omits the first column in each table.

Option Compare Database Option Explicit Sub ListTablesAndFields() 'Macro Purpose: Write all table and field names to and Excel file Dim lTbl As Long Dim lFld As Long Dim dBase As Database Dim xlApp As Object Dim wbExcel As Object Dim lRow As Long 'Set current database to a variable adn create a new Excel instance Set dBase = CurrentDb Set xlApp = CreateObject("Excel.Application") Set wbExcel = xlApp.workbooks.Add 'Set on error in case there is no tables On Error Resume Next 'Loop through all tables For lTbl = 0 To dBase.TableDefs.Count 'If the table name is a temporary or system table then ignore it If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _ Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then '~ indicates a temporary table 'MSYS indicates a system level table Else 'Otherwise, loop through each table, writing the table and field names 'to the Excel file For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count -1 lRow = lRow + 1 With wbExcel.sheets(1) .range("A" & lRow) = dBase.TableDefs(lTbl).Name .range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name End With Next lFld End If Next lTbl 'Resume error breaks On Error GoTo 0 'Set Excel to visible and release it from memory xlApp.Visible = True Set xlApp = Nothing Set wbExcel = Nothing 'Release database object from memory Set dBase = Nothing End Sub