SQL query using VBA Excel

Description

There are many ways to get data from external sources into a worksheet Excel . In this article, we'll look at how to query data from a database file MS Access and from a file Excel by using SQL in VBA . Yeah, we're going to mix it up a little bit SQL with VBA ! And it's better than you might think!

SQL stands for Structured Query Language (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 SQL query (variable varSQL ) to automate the retrieval of data from the database for the report. In the example below there is SQL query , which receives data from a small database in MS Access.

Click load the database MS Access (test.mdb) .

The MS Access file will look like this:

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

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

Sub SQLQuery_1()
'moonexcel.com.ua
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. Choose from the menu View - Macros (Alt+F8) the name of our macro " SQLQuery_1 ". A dialog box will appear, press it Database... , look for our Access file and click ОК .

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

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, the code of our macro needs to be slightly rewritten and the connection driver changed from Access to Excel.

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

Sub SQLQuery_2()
'moonexcel.com.ua
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 another extension). We find and select it. Let's press ОК , and we get the same data as from the Access file.

Related Articles:

• Import data from the Access database into Excel
• What is SQL? Basic concepts.