Hi,
I would like to share my file I’m working with. Basically it makes SQL requests to other excel file and return information I need.
This could be usefull for all who works with heavy excel files (50+ Mb) and have needs to get some data from this large files without opening it. Also this file have interesting functionality – to make similar requests to different files (in case you have many files with identical structure).
*This definitely is not ideal file for serious use and code could be much more elegant, but my purpose is to share main idea of queries from excel files
On the top of sheet SQL – small menu
1 – Button to choose the source File, data will be returned from File you’ll choose
2 – After File will be chosen this ComboBox will contain names of all sheets in the File
3 – After name of sheet will be chosen you will be able to choose witch columns from File you wish to load (names of columns are loaded automatically after Sheet name is chosen)
4, 5, 6 are optional, but if some criteria is needed – all cells in particular row has to be filled
4 – columns names (same as 3)
5 – pre-defined operators for criteria
6 – value of criteria
7 – if you need to make same requests from many files – Unckeck and just choose different file – all prev build Selection and criteria data will remain. In case this is Checked – after new file will be chosen – all Selection and Criteria info will be cleaned and you'll need to build Selection and Criteria again
8 – Button to Run query and get result
I’m attaching small dummy database file to explain how it works.
Example 1.
After I’ve chosen File – path to this file will appear to the right from button. I need to det data from sheet Data and from that sheet I need Columns „OrderDate”, „Region”, „Item”, „Units”. You can choose columns in any order you wish them to arrear below.
If I need just to get all data – I ‘ll press button 8 and get all records.
Example 2.
Same file, but sheet Customers. Columns under Select. Now I need to get records where values in Column „Region” is equal to „east”. If criteria is as string (not number) – you don’t need to use quaters or other symbols, just type it like on the picture.
Press button 8 and get result.
Example 3.
Sheets Data, Columns under Select. Now I need return data by two criterias: Rep and Units. As you can see you can choose criterias even if you don’t need to return data from this column (Rep=jones).
All multiply criterias working with AND between them, thats mean query will return data that meets two criteria at the same time.
Bookmarks