There have been a few occasions in the office where someone has presented me with an Excel Workbook which really should be a database; multiple sheets, all referring to another and a question like “I need to know the Sum of column x in sheet y but only where column a in sheet y has a value of less than 30 in sheet z….
Excel wasn’t designed to do that stuff!
Whilst this isn’t a scalable solution, and doesn’t beat migrating the Excel file into a proper database to support this kind of interrogation, here’s a method you can use to run SQL queries on a Excel file like it was a database.
Microsoft Office comes with a little known executable called MSQRY32.exe (at least on my 2007 version). It’s located in the same folder as the executable files for Excel, Word etc. [C:\Program Files (x86)\Microsoft Office\Office12] and can be run independently.
If you fire that up, you can select the data source for the query to be “Excel Files”, select OK and browse to your Excel workbook
Excel Sheets are modelled as “System Tables”, so you need to select options and tick the following box.
You’ll then see each sheet in the workbook displayed on the UI, you can add them to the Query, then use the graphical editor to create table joins etc.
Selecting the “SQL” button gives you the syntax required and after that, you can write whatever query you need to get the data out!