SQL query in Excel VBA

There are many ways to get data from external sources into a Excel sheet. In this article, we will look at how to query data from an MS Access database file and from a Excel file using SQL in VBA. Yes, we're going to mix some SQL with VBA! And it's better than you might think!

SQL stands for Structured Query Language and is a language used to retrieve information from databases (such as MS Access, MS SQL Server, Oracle, Sybase, SAP, MySQL and others).

You can also get data from the Internet, text files, or other Excel or CSV files.

1. SQL query from Excel VBA to file MS Access

So we need a database connection (variable varConn in the macro below) and an SQL query (variable varSQL) to automate the retrieval of data from the database for the report. The example below is an SQL query that retrieves data from a small database in MS Access.

Click download MS Access database. The MS Access file will look like this:

Let's write our macro that will execute the SQL query.

Open the menu Service - Macros - Editor Visual Basic, insert a new module (Insert - Module menu) and copy the macro text there:

Sub SQLQuery_1()
Dim varConn As String
Dim varSQL  As String

    Range("A1").CurrentRegion.ClearContents

    varConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)}"

    varSQL = "SELECT Month, Product, City FROM Sumproduct"

         With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
             .CommandText = varSQL
             .Refresh BackgroundQuery:=False
         End With
End Sub 

Click save and return to Excel. Select the name of our macro "SQLQuery_1" in the menu View - Macros (Alt+F8). A dialog box will appear, click Database..., look for our Access file and click OK.

Now we can see that our macro query has pulled the Month, Product, and City columns from the Sumproduct table from the test.mdb database into the Excel file.

2. SQL query from VBA to the closed file Excel

Similarly, we can get data from a regular Excel file as if from a database. For this, we need to rewrite our macro code a bit and change the connection driver from Access to Excel.

We export the Sumproduct table from Access to Excel and save the file. Next, we insert our changed code into a new module and run the macro:

Sub SQLQuery_2()
Dim varConn As String
Dim varSQL  As String

    Range("A1").CurrentRegion.ClearContents

    varConn = "ODBC;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}"

    varSQL = "SELECT Month, Product, City FROM Sumproduct"

         With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
             .CommandText = varSQL
             .Refresh BackgroundQuery:=False
         End With
End Sub 

A window will appear where we can select our Excel file (should be called Sumproduct.xlsx or with a different extension). We find and select it. Click OK, and we get the same data as from the Access file.

Articles on the topic: