Stop Confusing Power Query with VBA/Macros
Microsoft has been working hard on implementing query and data connection tools in its MS Office products. Now as a result we have a more mature data access tools in shape of Power Query. It is now more easy to connect to various database files outside the workbook in different formats. If you are working on a small or median range data, I would really recommend power query.
However lately I am getting the impression from various excel professionals that, in their opinion, power query is somehow a replacement or superior to VBA or Macros. Let me tell you, it is not. And it is not the same league. Both have different purposes. Previously people used Excel Macros or VBA to connect to database files because they did not have better data access options. For the purpose of query, of-course power query is better, but VBA is whole other story.
Power query is designed to automate data access and management. Power query is an advanced tool for basic users.
Excel VBA is designed to automate overall application including data access. VBA is a basic tool for advanced users.
The relationship between power query and VBA is same as C# and SQL. SQL is the most popular and power full database language but it can never be considered a replacement for a language such as C#. You design and manage databases using SQL but you design and manage applications using C#. Getting the point?
SQL is used in large, very large databases and as a matter of fact, I have been using SQL instead of tools like power query because I work on large data that cannot be easily handled by these tools. Recently I made a tool which is almost similar to power query but uses SQL and I use that (http://saber.pk/?p=238) instead of power query to avoid crashing, freezing, hanging and errors associated with power query. And unlike power query, SQL is not sequential in nature. You don’t have to first load a million rows and then apply filters 😉
Back to the point, consider this scenario.
- You want excel to import data from various files
- Perform some specific modifications
- Rearrange data
- Put the data back into several hundred new files i.e. row 1 to 50 in one file, 250 to 300 in another file, data with errors in another file, data with above/below specific figure in another file etc and you want to name all these files properly for further distribution.
- Meanwhile you want to print out parts of data on various conditions transforming along the way.
- And 50% of formulas in your workbook are custom made i.e. =Population(“Pakistan”,2008)
- You want automatically to turn calculations on or off while performing heavy tasks to avoid long processes.
- You want to output log of activities and errors
Now, this is not just data automation, this is application level automation which cannot just be done using power query.
You can use Power Query inside VBA to import data.
File creation is a filesystem operation however if the task is simple, you can call workbook.SaveAs i.e.
ActiveWorkbook.SaveAs Filename:=”C:\Book1.csv”, FileFormat:=xlCSVMSDOS, CreateBackup:=False
Then you want to automatically print out data choosing some of it and hiding other based on processed information, you can use sheets.printout
Worksheets. (“sheet1”).PrintOut From:=2, To:=3, Copies:=3
You can create custom formulas in Excel which can be used just like other formulas i.e.
And when you are automating all of your work and there are a lot of script going on on-top of your large data, you do not want excel to keep calculating and taking hours so before each process add
Application.Calculation = xlCalculationManual
and after the process add
Application.Calculation = xlCalculationAutomatic
Similarly you can log each and every task and error in a saperate file and save that file in a folder for later viewing.
This was an example which shows what is the purpose of Power Query and VBA and where each of those two stand.