As an Excel VBA developer in the corporate world, you spend a lot of time simply shuffling data around.
Pull data from a database here.
Send it to a database there.
Extract data from a text file here.
Import data into a spreadsheet there.
Data, data, everywhere
My number one tool for pulling data out of almost any source for well over 20 years has been ADO, or ActiveX Data Objects sprinkled with some SQL and VBA.
Introduced by Microsoft in 1996, ADO became the standard way to connect to databases using VBA.
Those days are over.
I even went as far in a recent post to claim that ADO was faster than linked formulas or Power Query (PQ). Then a follower on LinkedIn questioned my assertion, so I did some homework, and the answer is; it depends.
Here's what I found:
ADO with VBA (SQL Query)
From a pure performance perspective, ADO is slightly faster for smaller datasets, but can be inefficient for very large ones. Having said that I’ve pulled tens-of-thousands of rows from corporate databases into Excel in 2 seconds flat using this approach.
However, from an ease-of-use perspective, this approach requires skills in both VBA and SQL.
To automate the data extraction process also requires VBA knowledge.
Power Query
From a performance perspective, Power Query has been optimised for handling larger sets of data more efficiently.
For many people, extracting data via PQ is simply a process of point-and-click. You won’t need to get your hands directly under the bonnet (hood).
And to automate the data extract, you can trigger that manually or have Excel do it each time you open the workbook. And if you *really* want to refresh PQ via VBA a quick “ThisWorkbook.RefreshAll” will do that.
Conclusion
So, from a performance perspective, Power Query tends to have the upper hand. Where it provides the knock-out blow for most Excel users is the sheer ease-of-use.
Point.
Click.
Extract.
However, what the ADO+SQL+VBA approach does have is fine granularity and control that Power Query lacks (you can see I’m not going to let go of this one without a fight).
The ADO+SQL+VBA approach allows you to extract 20 single values into your workbook from 20 different sources with precision.
Finally, Power Query is a one-way street. You can retrieve data into Excel but not send it back again. This is not the case with ADO+SQL+VBA - her, you have complete control, both retrieving and updating data from almost any data source.
What’s been your experience? Are you a Power Query convert or do you use the ADO+SQL+VBA wonder triplets to get data into your spreadsheets? Let me know in the comments below.
0 comments