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.
<pre>Sub testADOCopyCurrent()
Dim ado As New cADO
ado.init().execute
End Sub</pre>
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
Selectively Copy
Now let’s do the same thing to selectively copy using one of the columns as a filter.
<pre>Sub testADOCopyCurrentFilter()
Dim ado As New cADO
ado.init().execute , , "where value=1"
End Sub</pre>
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
<pre>Sub testADOCopyCurrentFilterSpecify()
Dim ado As New cADO
ado.init(Range("testAdo!a1")).execute , , "where value=1"
End Sub
</pre>
Specify an input sheet
Now do the same thing, but instead of using the current sheet as input, use a specific one.
<pre>Sub testADOCopySpecificFilterSpecify()
Dim ado As New cADO
ado.init(Range("testAdo!a1")).execute "tweetsentiments", , "where value=1"
End Sub</pre>
Only copy some columns
By default, all columns will be copied. Here’s how to specify just a couple of columns.
<pre>Sub testADOCopySpecificFilterSpecifyCols()
Dim ado As New cADO
ado.init(Range("testAdo!a1")).execute _
"tweetsentiments", "name,phrase", "where value=1"
End Sub</pre>
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
<pre>Sub testADOCopySpecificFilterSpecifyColsAdd()
Dim ado As New cADO
ado.init(Range("testAdo!c1"), , False).execute _
("tweetsentiments", "value", "where value=1").kill
End Sub</pre>
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.
<pre>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</pre>
creates this output
<pre>{
"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"
}
]
}</pre>
Make a Duplicate
cDataSet can also make clone of itself, So you could make two copies of the data.
<pre>Sub testADOCopySpecificFilterSpecifyColsClone()
Dim ado As New cADO
ado.init(Range("testAdo!a1")).execute _
("tweetsentiments", , "where value=1").dSet.bigCommit _
wholeSheet("anotherClone")
End Sub</pre>
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
<pre>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</pre>
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.
<pre>Sub testADOaccess()
Dim ado As cADO
Set ado = New cADO
ado.init(Range("testadoaccess!a1"), _
"D:googlechartsqlTest.accdb").execute _
"dogOwners", "firstname,lastname", "where dogowner=true"
End Sub</pre>
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 picking up data from a sheet of a closed workbook, converting it to jSon and serializing the output for display.
<pre>Sub testADOclosed()
Dim ado As cADO
Set ado = New cADO
Debug.Print _
ado.init(Range("testadoclosed!a1"), _
"D:googlechartroadmapgenerator.xlsm").execute( _
"inputdata").dSet.jObject.Serialize(True)
End Sub</pre>
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
<pre>Public Enum eAdoConnections
eAdoAuto
eAdoExcel2007
eAdoAccess2007
eAdoUnknown
End Enum</pre>
And they are selected through the interface to the .init method
<pre>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</pre>
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
<pre>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</pre>
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.
<pre>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))</pre>
Summary
There are many more connections that can be simply added to this library, and I think that the abstraction of connection from data location from process makes for a very simple usage model. In the meantime why not join our forum to ensure you get updates when they are available. You can also submit anything you want considered for publication on this site (for example if you add interesting connections to the cADO Library). If you haven’t read about the cDataSet model, I suggest you next take a look at How to use cDataSet.