Execute SQL from Excel

What can you learn here ?
  • Library of ADODB connections
  • Populate from databases
  • Use SQL on closed workbooks

A library to populate Excel tables with SQL query results get it now

Using SQL to populate Excel tables gives you another level of data abstraction. Combined with  How to use cDataSet and the capabilities that are inherent in those classes means that we can do complex queries and conversions from various inout data sources, including Sql Databases such as Access, for example to jSon, with not much more than a single line of VBA code. 

As with other examples on this site such as Rest to Excel library and Regular Expressions library, I found that implementing an ADODB library with the connection strings defined in there, abstracts away the complication of figuring out how to get data from various sources.

This section introduces a new class; cAdo,  another module adoExample showing how to use these. 

Try it out first ?

These are enhancements to Data Manipulation Classes so they have been included in cDataSet.xlsm, which is downloadable from Download Complete Projects

An ADODB Example

Let's take a look at the code needed to copy the current worksheet to another sheet, using ADODB.
Sub testADOCopyCurrent()
    Dim ado As New cADO
    ado.init().execute
End Sub

that's all there is to it. In this case it will copy the currently active sheet and create a new one with a duplicate of the data. In the following examples, look closely at what code changes between examples to see how to perform more and more complex actions in a single line of code. For these following examples I am using mainly data from the tweetsentiments example from the Rest to Excel library. You can read about it in this ramblings blog post on tweetsentiments. It is also in the cDataSet.xlsm in Download Complete Projects 

Note: Tweetsentiments.com has withdrawn free access to it's API. This example needs updating to use a different dataset as an example. 

Selectively Copy

Now let's do the same thing to selectively copy using one of the columns as a filter.
Sub testADOCopyCurrentFilter()
    Dim ado As New cADO
    ado.init().execute , , "where value=1"
End Sub

Specify output starting cell

In the previous cases, A new worksheet would have been created each time it was executed. In this case we'll specify a place to start putting the output data at. There is also an option to clear the worksheet first, or to leave the other cells intact. By default the sheet will be cleared first. We will also leave in the filter as before
Sub testADOCopyCurrentFilterSpecify()
    Dim ado As New cADO
    ado.init(Range("testAdo!a1")).execute , , "where value=1"
End Sub

Specify an input sheet

Now do the same thing, but instead of using the current sheet as input, use a specific one.
Sub testADOCopySpecificFilterSpecify()
    Dim ado As New cADO
    ado.init(Range("testAdo!a1")).execute "tweetsentiments", , "where value=1"
End Sub

Only copy some columns

By default, all columns will be copied. Here's how to specify just a couple of columns.
Sub testADOCopySpecificFilterSpecifyCols()
    Dim ado As New cADO
    ado.init(Range("testAdo!a1")).execute _
        "tweetsentiments", "name,phrase", "where value=1"
End Sub

Clean up

After making an ADODB, we should really close the connection and clean up. Here's an example of adding to the data on a sheet (as opposed to clearing the sheet first), and then cleaning up afterwards
Sub testADOCopySpecificFilterSpecifyColsAdd()
    Dim ado As New cADO
    ado.init(Range("testAdo!c1"), , False).execute _
        ("tweetsentiments", "value", "where value=1").kill
End Sub



Using cDataSet capabilities. 

Data Manipulation Classes are used throughout this site to abstract data from its physical location. This new class, cADO, also creates a cDataSet of what it has just created through an SQL operation. This means that you can apply all the cDataSet capabilities to that copied data - for example

Convert to jSon

cDataSet has the capabality to convert to jSon - so this means you could take sql input and convert it to json in one line of code. 

Sub testADOCopySpecificFilterSpecifyColsjSon()
    Dim ado As New cADO
    Debug.Print ado.init(Range("testAdo!a1")).execute _
        ("tweetsentiments", , "where value=1").dSet.jObject.Serialize(True)
End Sub

creates this output
  {
   "cDataSet":[      {
         "phrase":"I am happy",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"Merry xmas",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"I enjoyed our talk",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"I ate nice food",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"I lost a lot of money at the casino",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"I won a lot of money at the casino",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"the picture quality is excellent",
         "name":"Positive",
         "value":"1"
      },
      {
         "phrase":"great show",
         "name":"Positive",
         "value":"1"
      }
    ]
 }

Make a Duplicate

cDataSet can also make clone of itself, So you could make two copies of the data.
Sub testADOCopySpecificFilterSpecifyColsClone()
    Dim ado As New cADO
    ado.init(Range("testAdo!a1")).execute _
        ("tweetsentiments", , "where value=1").dSet.bigCommit _
        wholeSheet("anotherClone")
End Sub

Creating a list of unique values

cDataSet can make a collection of unique values in a column, and sort them if required. Here's how to do that, then print them 
Sub testADOCopyColsUniqueValues()
    Dim ado As New cADO, u As New Collection, cc As cCell
    With ado.init(Range("testAdo!a1")).execute _
            ("tweets").dSet.Column("from_user_name")
        For Each cc In .uniqueValues(eSortAscending)
            Debug.Print cc.toString
        Next cc
    End With
End Sub

Taking data from other sources.

So far we've looked at using SQL and ADODB to take data from the workbook we are currently working on. In the cDataSet.xlsm example you have downloaded, all the examples mentioned to now are working within that workbook. Now we are going to look at taking input data from outside the current workbook, and for this you will need to provide your own datasources, and amend the code accordingly. 

Get data from an access 2007 database

The only difference from what we have seen already is that here we provide the name of an access database. The first argument to the execute method in this case is the name of an access table (or query) as opposed to the name of a worksheet.  All the previous comments apply equally, regardless of the datasource. 

Sub testADOaccess()
    Dim ado As cADO
    Set ado = New cADO
        ado.init(Range("testadoaccess!a1"), _
            "D:\googlechart\sqlTest.accdb").execute _
            "dogOwners", "firstname,lastname", "where dogowner=true"
    
End Sub

Get Data from a closed Excel 2007 workbook

Similarly, you can just provide the name of a workbook as a data source. Here is an example of pickign up data from a sheet of a closed workbook, converting it to jSon and serializing the output for display.
Sub testADOclosed()
    Dim ado As cADO
    Set ado = New cADO
    Debug.Print _
        ado.init(Range("testadoclosed!a1"), _
            "D:\googlechart\roadmapgenerator.xlsm").execute( _
            "inputdata").dSet.jObject.Serialize(True)
    
End Sub

Using  the cADO library

As mentioned previously, the cADO takes the form of a library of known connections. So far I have only setup a couple of formats, but it is simple to add more. Where possible, initializing the cADO class will cause it to try to detect the correct connection string to use, but that can be forced if necessary.

These are the currently implemented connections
Public Enum eAdoConnections
    eAdoAuto
    eAdoExcel2007
    eAdoAccess2007
    eAdoUnknown
End Enum


And they are selected through the interface to the .init method
Public Function init(Optional rOutRange As Range = Nothing, _
                      Optional sDataSource As String = vbNullString, _
                      Optional bClearsheet As Boolean = True, _
                      Optional bCreateDset As Boolean = True, _
                      Optional eConnection As eAdoConnections = eAdoAuto, _
                      Optional complain As Boolean = True) As cADO

with eAdoAuto being the default. When this is provided, the type of connection to use is detected by the value of the sDataSource argument using 

Private Function tryToGetConnectionType() As eAdoConnections
    Dim p As Long
    tryToGetConnectionType = eAdoUnknown
    p = InStrRev(pDataSource, ".")
    If p <> 0 Then
        Select Case Mid(pDataSource, p + 1)
            Case "xlsm", "xlsx", "xlsb"
                tryToGetConnectionType = eAdoExcel2007
            
            Case "accdb"
                tryToGetConnectionType = eAdoAccess2007
            
        End Select
    End If
    
End Function

Adding your own entries to the ADODB library

Firstly you should decide whether eAdoAuto could work on your data source. If it is possible to detect from the datasource name as it is for access and excel, you could update the tryToGetConnectionType to be able to automatically detect the type, which you would also add to the eAdoConnections eNum.

If it is not possible to detect automatically, then the argument will need to be provided to the .init method to select your custom datasource. 

Next you need to handle the connection by researching what the connection string should look like, and construct the SQL syntax - which will likely be the same as one of the others. 


These are the entries in the .execute method for Excel and Access2007. You just need to copy these and tweak for your own entry.
        Case eAdoExcel2007
            cString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                      "Data Source=" & pDataSource & ";" & _
                      "Extended Properties=""Excel 12.0;HDR=Yes"";"
            pSQL = Trim("select " & thisOrThat(sSqlFields, pSQLFields) & " from [" & _
            thisOrThat(sTable, pTable) & "$] " & thisOrThat(sSqlExtra, pSQLExtra))
            
        Case eAdoAccess2007
            cString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                      "Data Source=" & pDataSource & ";" & _
                      "Persist Security Info=False;"
            pSQL = Trim("select " & thisOrThat(sSqlFields, pSQLFields) & " from [" & _
            thisOrThat(sTable, pTable) & "] " & thisOrThat(sSqlExtra, pSQLExtra))

Comments