Excel 2016 Late Binding ADO - SQL query on CSV file to get named currency

Question

I will be grateful if someone will give me the SQL to get say all USD data from a CSV file into Excel when one is using Late Binding ADO.  I can hook into the file and get all the data with "SELECT * FROM" etc but i cannot get it to just return USD data.  I can get it to work with Early Binding but not Late Binding.

If one is importing from Access for example one would put the currency in single quotes as say "SELECT * FROM mytable WHERE Currency = 'USD'".

best regards

Spike

Answer 1

clearly i had something in the wrong place in my code as 'GBP' works as does putting the currency into a variable!!

spike

Answer 2

Thank you for your advice re Get and Transform which i have not used before and it certainly is a handy tool which i will be using in the future but will persist with the Late Binding ADO on this matter if i can find a solution of course.

best regards and thanks again

Spike

Answer 3

That's a shame to hear, I certainly don't miss the ADO library in the slightest. The Get and Transform / PowerQuery solution is only available for Excel 2013 and forward so those legacy codebases will still require ADO. 

That being said, for dynamic data linkage through the ADO, my approach was generally to use a VBA subroutine to edit the primary connection string for the top level connection. The individual query strings could be set up the same way with the basic query written out as a string with different user inputted components - such as swapping "USD" with "EUR" or "AUS" in your example, modifiable through a VBA subroutine that reads or takes some input.

Perhaps this might help:

https://stackoverflow.com/questions/20114223/modify-an-embedded-connection-string-in-microsoft-excel-macro

It's not a true late binding as you're basically creating a work-around that re-points your early binding on command, but it can give you dynamic access to multiple data through a single user touch-point and works well as a mini Ribbon addition.

I can't even seem to figure out how to find those connection strings in Excel 2016 anymore .. I keep ending up in PowerQuery .. otherwise I'd try to offer some better help.

Good luck!

Answer 4

Thank you that looks really interesting and i will definitely play with it; however the reason i need to do this using Late Binding is so that this will work in older versions of Excel and i am not sure if 'Get and Transform' is available on previous versions (i only have 2016) and i also need to fully automate this so it is usable by any user with previous versions of Excel.

Also as a matter of personal interest i would like to know how to do this using ADO with Late Binding.

very best regards

Spike

Answer 5

I would recommend Get and Transform as an alternative. I've replaced effectively all of my ADO connections with Get and Transform functions in one way or another and have found it to be a godsend for creating queries off of a variety of different data sources with older MS Access (2003 and prior) databases being the lone exception.

It would shift your query writing out of SQL into the M Language but the provided GUI makes learning it super fast and easy while giving you more flexibility over the types of data sources that you want to interact with while simplifying a lot of the underlying aspects like connection management.

To allow for late binding with Get and Transform for a workbook, in the Power Query / Get and Transform "Query Options" set:

Global => Power Query Editor => [ check] Always allow parameterization in data source

Current Workbook => Data Load => Background Data => [ uncheck] Allow data preview to download in the background. [if you're changing your data source frequently, this will save you much file bloat and headache]

With that, you can make the data accessible via the Get and Transform interface really easily and then apply all the filters / interactions you want (and easily transition it to Power BI if you decide to later). Here's the basic M Query for the parameterized data source which can be followed up with parameterized filters and custom reports. 

The parameter can be another Excel table so that the source can be modified by anyone willing to edit a cell, or you can use a query parameter (as shown below) that requires opening the Get and Transform interface to modify.

In the image below, FileSource = "C:\Users\Michael\OneDrive\Documents\Answers\CSVs\QuantityCSV.csv" 

I offer an introductory and advanced Get and Transform course through my website so I'm somewhat partial to the technology and absolutely do not miss my days fiddling with ADO or other forms of external connections to get data.