Executing SQL from Excel and Data Abstraction

Abstracting an Excel data source.

In almost of all of the downloadable examples on the ramblings web site, I use a set of data abstraction classes to separate workbook data from its workbook location, and as a result, these classes now have a fair amount of capability built in such as Excel-jSon-Excel conversion, getting data from Google Docs and many other functions.

Most of you will already know about how to get data into Excel from an SQL database, whether through ODBC, MS Query, ADODB, the Excel toolbar and many other methods.

Getting data into excel using SQL can be kind of complicated, with all the confusion around connection strings and recordsets and so on, so I thought I could combine the ideas of creating a simple library class to connect to various sql data sources of the type implemented in the rest-Excel library, and the Excel regular expression library along with the data abstraction classes, so that they could be used to create really simple VBA functions.

As with previous posts such as  Rest-Excel in one line of code and excel-Json in one line of code , I set myself the target of being able to execute SQL on a variety of data sources, populate Excel worksheets, and do various conversions such as to jSon, all in just one line of VBA code. You can download the resultant classes and examples and read in more detail on the ramblings web site, but here are some examples using the cADO library class.

Some examples

Copy the current worksheet to another sheet, using ADODB.
Copy selected columns of filtered data from one sheet to another, using SQL/ADODB, then clean up the connections afterwards

Copy a worksheet, using SQL filtering, convert it to jSon, and serialize the jSon nicely formatted as per jSonLint.

Get data from an access database using sql, and populate a worksheet

Get data from a closed Excel workbook, populate a worksheet, and convert it to jSon, serializing the output nicely formatted for display.

 

About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


four + twelve =