Call
613-749-4695

Click to Email
[email protected]

Or let's have a live conversation computer to computeer for free:

Skype ID:
peter-excel-vba

Here is a sample of what you will find in lesson 22 of the downloadable Tutorial on Excel macros

Lesson 22 on Excel Macros (VBA):

VBA Code and  SQL

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.

Click here to download the small Access database and test the following code from a workbook sitting in the same folder.

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

Open the Excel files vba-sql1 and vba-sql2 for a complete explanation of the code and much more on queries. These two Excel workbooks are part of the Tutorial on Excel Macros.

We hope you have enjoyed this introduction to lesson 22
for more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros

Next Lesson: VBA Code for other Purposes