Microsoft Excel SQL Versus VBA - an instance of Turing Completeness? A.K.A. Excel Rows to Sheets

I have created an 18 minute illustrative video to accompany this tutorial here

By an accountant - how's that relevant?
My focus is different, my purpose is the results not the process.

1
Why this subject?
I find a constant need to get data from banks, credit cards and Paypal into Excel. This sort of process allows me to do so without investing time in VBA.

2
Who is this for?
Anyone needing re-organise data in Excel based on filtering beyond the norm and in multiple sheets.
While I am accounting focussed, anyone who needs to do what it looks like Pivot Tables might do and then find they do not may find this useful.


3
Where? Country:

This will work for anyone anywhere,


VIEW POINT

From the point of view of an ordinary "point and click" user who for whatever reason has no desire to employ a "Turing Complete" solution (i.e./e.g. VBA).

CONSTRAINTS

Permitted: MS Excel.
Permitted: Point & click only.
Interdit: VBA. Etc.


REQUIREMENT

Find a way to make Excel Get (External/Filter) Data (a.k.a. MS Query) activate automatically and do so multiple times (a procedure) without using a procedural language so as to be able to makes Rows of data auto transfer to multiple sheets.


WHAT'S SPECIAL ABOUT THIS?

Create MASS (MULTIPLE AUTOMATIC SIMULTANEOUS SQL) Queries in Excel with MS Query; which ordinarily lets you interact only one Query at a time.

Generate MS Query output to multiple Excel sheets, i.e. 3D reports; MS Query outputs to one sheet only = 2D.

Report upon data that PivotTable Reports would need helper columns and/or complete re-organisation to get the field content into one column.

Replicate Pivot Report Page Field multi sheet output with overlapping multiple Page Field criteria. E.g. Crit 1:Thursday = x and then using same Thursday field and also Friday field Crit 2: Thursday = X and Friday = X. A PT cannot use the same Field twice with different criteria.



STRATEGY

Given that:

1-MS Query can be saved into saved DQL query files: all point and click and save to file. No programming.

2-MS Query outputs to ONE only sheet in an Excel file PER QUERY, SO generate multiple queries so as to be able to specify multiple output sheets, one per query. WITHOUT VBA.

3-MS Query properties allow specification of WHEN queries shall run based on Excel activity, we can use Excel itself to complete the "Turing Complete" requirement. So we ARE using a procedural language (i.e. Excel itself), but only at the highest user level. This is programming without programming.

CASE STUDY

Someone has come up with the perfect question to use as an example:

Excel 2010 - VBA or Not VBA? - Entry in Column C triggers copy/link into another sheet
Asked by: anon for privacy

I have a worksheet with a sheet named "Summary"
I would like to have the entire line copied to the Thursday sheet if there's an X in the Thursday column
I would like to have the entire line copied to the Friday sheet if there's an X in the Friday column.
I would like to have the entire line copied to the Thursday and Friday sheet if there's an X in the Thursday and Friday column.

Can someone help me with the code for this?
Or perhaps this is solvable without VBA?
copy-rows-to-sheets.xlsm


The very clever original solution was a VBA/SQL mixture where VBA was used to provide the Turing Completeness needed to apply the SQ, written by fanpages.


Please bear in mind that this is not a contrived set of test data, it is "as presented", so the video has that feeling about it caused by having to think on the fly. I hope this makes it a bit more "live". The "m" on the end of the filename extension viz xlsm means there is a macro in the file, however the macro is not relevant to this solution.

The above Question requires ALL the constraints I have described above, hence an excellent choice for this case study.

TUTORIAL INSTRUCTIONS


Open a New Excel file.

If helpful also open the file(s) that contain the data to be queried (summarised/analysed)
Create the output sheets' tabs with desired names in the Excel file you want to hold the output. It can be useful to just make a note of the criteria you want to apply, as an aide memoire.

As such we have now an input file with data and an output file to receive the results.


Excel 2010 Menus click as follows:

Data
From Other Sources
From Microsoft Query FEAR NOT! We will at no time depart from the Excel user interface.

then in

Choose Data Source dialogue:

Excel files
OK

Select Workbook dialogue*:

Select
yourdata.xls (in the video this file is called rows-to-sheet.xlsm (it can be xls, or xlsx etc the "m" is not relevant)
OK

Query Wizard - Choose Columns

The sheets within the file you selected above are listed.
Select the sheet that contains your data  

Click " > " which transfers all the column headings in to the right hand list box.

Click Next

Query Wizard - Filter Data

Select the column you want to select data from - in my example Thursday
Select "equals" from the drop down box and enter X into the next input box. So reporting all Thursdays with an X.

Click Next

Query Wizard - Sort Order

Click Next (no sorting)

Return Data to Microsoft Excel (pre-selected)

Click Save Query

Save the query in the standard file save way, with the Excel files is my suggestion while learning. There will be two more of these before we finish, so using 01, 02 and 03 IN FILE NAMES is useful.


Click Save

Click Finish (but it does NOT finish here, keep going)


to

Import Data dialogue:

Select target for report, I am using A1 of my desired "report" sheet which is where I left the cursor before starting all this so it would default to that location at this point.

Do NOT click "ok"

Click Properties

Connection Properties dialogue

Enable Background refresh to refresh when changes are made in the data

AND/OR in this case study example

Click "Refresh data when opening file" - so that opening this file will run the query procedure (despite the fact we have not written a procedure - created one perhaps, but not written - No VBA!).

The video has a look at the other options.

At this point we re-iterate the above process twice more.
It gets much faster with practice, such that creating these on-the-fly can become a trivial matter.

for the second and third queries, the only differences being as follows:

1-Before starting each query creation process, select A1 in your desired report output sheet (it makes the end process easier is all), give the sheet a name.

2-the third query has two Filter Data needs, not just the one. For the second select the second Column Heading to be filtered in addition to the first and repeat the equals X entries.

3-Save each query as a separate file, do NOT over-write your previously saved queries.

The video also shows a check process (change the data) to prove it is all working: time index 13:20

Save the Excel file as whatever name you wish, preferably into the same folder as the Query dql files.

Save and Close all.

Open the file you just saved and your sheets will be automatically populated with your data .


All done. No VBA.

The video shows every step of the above and more, so would best be viewed in conjunction with this text.

Good luck!

Anthony