Power of Excel VBA (part 1)

This blog is mostly about Power BI, but I spent many years working with Excel VBA before I switched to working with Power BI full time and I still have some knowledge that will be useful for others. I think I have to share and post a few articles about Excel before I started forgetting VBA…

Let’s say there are 2 tables (tSrc1 with name and value1 columns and tSrc2 with name and value2 columns) and empty tResult table (with name, value1, value2, value3 columns):

How to merge the tables so we have all names from the fist table, value1, value2, value3 = value1*value2 in one table?

As always in Excel, there are multiple solutions, for example formulas based on INDEX and MATCH functions.

Can we join the tables using VBA to get automated solution? Surely.

My VBA code to join the tables:

Sub JoinTables()

    Dim sql As New clsSQL 'my ADO based class module
    
    sql.OpenConnection 'to this workbook

    'SQL query to merge the tables
    sql.q = "SELECT A.[name],A.[value1],B.[value2],A.[value1]*B.[value2]"
    sql.q2 = "FROM [[tSrc1]] A LEFT JOIN [[tSrc2]] B"
    sql.q2 = "ON A.[name]=B.[name]"

    sql.Insert [tResult] 'insert result in a table

    Set sql = Nothing 'close connection

End Sub

Result table:

Do you like the solution? Do you want to read more about my solution (and my magical clsSQL class module)?