VBA Lesson 22: External Data and SQL in VBA for Excel

This is an excerpt from Pierre Leclerc (www.excel-vba.com)

You have seen in lesson 17 how to use Excel database functionalities with VBA (sort, filter, etc...). In this lesson we discover how to use VBA to extract data from external databases.

With VBA you can use the powerful importing functionalities of Excel. You can even design queries to extract data from any external database submitting criteria directly in Excel. SQL is the language used to extract data from a database through a query. Do you need to learn SQL? The answer is NO.

The database people can develop the SQL sentences for you or you can use Access or any query builder program to do so easily (WISIWIG).

Once you have the SQL sentence you can connect to any database.

Here is a basic piece of code to connect to an Access database and extract data using SQL. The table's name is tbDataSumproduct and the three columns(fields) containing the data are Month, Product and City.

Sub proSQLQuery1()
Dim varConnection
Dim varSQL

       Range("A1").CurrentRegion.ClearContents

       varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\test.mdb; Driver={Driver do Microsoft Access (*.mdb)}"

       varSQL = "SELECT tbDataSumproduct.Month, tbDataSumproduct.Product, tbDataSumproduct.City FROM        tbDataSumproduct"

       With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=ActiveSheet.Range("A1"))
               .CommandText = varSQL
               .Name = "Query-39008"
               .Refresh BackgroundQuery:=False
       End With

End Sub

Go to the next lesson
Lesson 23 : Other Microsoft Programs in VBA for Excel