How to Merge Data from Two Excel Worksheets

How to merge data from Two Excel worksheets in a new workbook and create a new database using Microsoft Query. We also record the macro while we are performing the actions to merge the data from the two worksheets in a new file. We then edit the recorded code and finally  assign the macro to a button control. If you now enter more data into your database in the student-data file and run the macro again the new data will also be incorporated into the database. Watch the video below:

 

Watch this video on YouTube.

Here’s the complete VBA code to merge data to create new database:

Sub Macro1()

‘ Macro1 Macro
‘ HOW TO MERGE DATA FROM TWO WORKSHEETS IN AN EXCEL WORKBOOK


Application.CutCopyMode = False
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
“ODBC;DSN=Excel Files;DBQ=C:\Users\takyar\Desktop\student-data.xlsx;DefaultDir=C:\Users\takyar\Desktop;DriverId=1046;MaxBufferSize=20” _
), Array(“48;PageTimeout=5;”)), Destination:=Range(“$A$1”)).QueryTable
‘.CommandType = 0
.CommandText = Array( _
“SELECT `Sheet1$`.ID, `Sheet1$`.`First Name`, `Sheet1$`.`Last Name`, `Sheet2$`.`Attendance %`, `Sheet2$`.`Marks %`” & Chr(13) & “” & Chr(10) & “FROM `C:\Users\takyar\Desktop\student-data.xlsx`.`Sheet1$` `Sheet1$`, `C:\Users\takya” _
, _
“r\Desktop\student-data.xlsx`.`Sheet2$` `Sheet2$`” & Chr(13) & “” & Chr(10) & “WHERE `Sheet1$`.ID = `Sheet2$`.ID” _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = “Table_Query_from_Excel_Files”
.Refresh BackgroundQuery:=False
End With
Range(“F13”).Select
End Sub

While entering the code we should be careful about the quotes and apostrophes otherwise we will get errors.

 

Leave a Reply

Your email address will not be published. Required fields are marked *