Lesson 22: External Data and SQL in VBA for Excel

SQL stands for Structured Query Language and is the language used to extract data from almost all databases like Access and SQL Server from Microsoft or, Oracle, Sybase, SAP and also most accounting applications. You can also extract data from the Internet, from text files and from other Excel or CSV files.

Basically you need a connection (varConn in the macro below) and an SQL sentence (varSQL in the macro below) to automate the extraction of data for reporting purposes. In the example below an SQL query extracts all the data from a small Acces database.

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

    Range("A1").CurrentRegion.ClearContents

    varConn = "ODBC;DBQ=test.mdb;Driver={Driver do Microsoft Access (*.mdb)}"

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

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

End Sub

I am in a plan to put a list of post on VBA+SQL and other database connectivity. Please visit those tutorials for advanced database connectivity with VBA Excel.