Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-12: External Data and SQL in VBA for Excel
(Levels: Advanced)

In alphabetical order here are the 6 VBA words that you need to learn to work efficiently with outside databases and datasets:

Add, Connection,  Destination, QueryTables, Refresh, Sql

SQL stands for Structured Query Language and is the language used to work in almost all databases. From Excel with SQL you can extract data from any database, text file and other environments.

In the example below I needed to develop a query to extract data from a CSV file with 200,000 lines. I cannot get all 200,000 lines in Excel but with the following code I extract the unique values (no double) from the file.

To query data you essentially need 2 things a connection (varCon) and an SQL sentence (varSql).

To get the connection use the Macro Recorder while going to "Data/Import External Data/New Database Query" .

For the SQL sentence you can use the same approach (Macro Recorder) or use Access. Create a query in Access, copy the SQL sentence created by Access and paste it into your VBA for Excel code.

In the following code the file that data are extracted from is named "Generic Call Detail Report Rev.csv" , the sheet where the data reside is "Generic Call Detail Report Rev" and the field from which I am extracting data is "Subaccount #:"

Sub proQuery()

Dim varConn As String
Dim varSql As String
Dim varQuery As QueryTable

          varConn = "ODBC DefaultDir=E:\Garson Driver={Microsoft Text-Treiber (*.txt           *.csv)} DriverId=27 FIL=text MaxBufferSize=2048 MaxScanRows=8 PageTimeout=5
          SafeTransactions=0 Threads=3 UID=admin UserCommitSync=Yes "

        varSql = "SELECT DISTINCT `Generic Call Detail Report Rev`.`Subaccount #:`
        FROM `Generic Call Detail Report Rev.csv`       `Generic Call Detail Report Rev`"

      Set varQuery = ActiveSheet.QueryTables.Add( Connection:=varConn,
      Destination:=Range("a1"), Sql:=varSql)

        varQuery.Refresh

End Sub

Once the query has been created here is the code to refresh the data. You set the curser anywhere in the database and execute this small piece of code.

Sub proRefresh()
        Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple