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.
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.
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.