Excel VBA tip 7: Create a recordset based on 2 different worksheets

This is the seventh and last blog post in our series about how you can speed up data transformation in Excel VBA.

1. Copy cells with VBA
2. Add data to a table
3. Open a file and transfer data to a target file
4. Open a file and transfer data to a destination file
5. Save data in a recordset
6. Use a recordset to read data from multiple files without opening them
7. Create a recordset based on data from 2 different worksheets

In this blog post, we’ll discuss how you can merge two tables and add them to one recordset. Both tables need to contain at least one common field. This field is used as a link between both data areas. We’ll also show you how to view and edit the data.

The concept of a recordset has been discussed in the previous blog posts. This time, we’ll only have to focus on how the query is written. After that, we’ll view and evaluate the recordset, after which the adapted result will be stored.

In the first example, the merged data will be copied to a new worksheet. In the second example, we’ll process the records separately.

The image below shows the data we’re using and the result.

Every panel in the image is on a different worksheet. The right-hand panel shows what it looks like when the code is entered in an existing empty worksheet.

Note: Make sure to delete the previous results before you start a new run or integrate this action into your code to avoid having to intervene manually.

Sub CopyWithADODB()
‘ Reference to: Microsoft ActiveX Data Objects 6.1 Library
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Dim strPath As String
Dim wsMain As Worksheet
Debug.Print Now
Set wsMain = Worksheets(“Sheet3”)
Application.ScreenUpdating = False
strPath = ActiveWorkbook.FullName
myConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & strPath & “;Extended Properties=Excel 12.0”
mySQL = “SELECT [Sheet1$].[Code],[Sheet1$].[Price] , [Sheet2$].[Units], [Sheet2$].[Tax] ” & _
“FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Code] = [Sheet2$].[Code]”
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenForwardOnly, adLockOptimistic
wsMain.Range(“A1”).CopyFromRecordset RS
RS.Close
Set RS = Nothing
Application.ScreenUpdating = True
Debug.Print Now
End Sub

De SQL code wordt als volgt samengesteld:

“SELECT [Sheet1$].[Code],[Sheet1$].[Price] , [Sheet2$].[Units], [Sheet2$].[Tax] ” & _
“FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Code] = [Sheet2$].[Code]”

If you look at the spelling, you’ll recognize the spelling Excel uses for forms in tables. We’re just using the worksheet name and the name (title) of the column instead of the table name and column name.

This is an excellent alternative for users who can’t or don’t want to change to Power BI. With enough knowledge of the SQL query language, you’ve got plenty of options to merge and analyse data. This method is as old as Excel itself.

In the next example, we’ll multiply the unit and price and use the results instead of using the two base values separately.

Note: There are several options to do this, but for this blog, I chose to loop the recordset and store the result of the calculation in Excel. Another solution would be to have a recordset with an extra column in which the operation is saved. After going through the recordset, you can paste the entire thing in Excel. Or you could overwrite an existing column with the solution to avoid creating another column, but then you’d lose your original base data.