SQL.REQUEST Function for Excel - Very Useful!!!

[Bowers74]

I would like to inform you about an Excel function not many know about that helps you to return smaller amounts of data from a database:

The SQL.REQUEST() function gives you SQL Query functionality directly in Excel!

The use of this Function is a little in depth, so I am not going to try to explain it all in this thread.

If you want to know more about this function then go to the following website:

http://www.bygsoftware.com/examples/sqlrequest.html

You can download an example Workbook at this link:

http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip

This is a great example and definately worth looking into. ;-)

You will need to activate or install the ODBC Add-in (XLODBC.XLA) to be able to use this function.


For those of you who have Excel XP, you need to know that the Excel ODBC Add-in is no longer included with Office XP, but you can download it from MS at this link:

http://download.microsoft.com/download/excel2002/xlodbc/2002/w98nt42kmexp/en-us/xlodbc.exe

I have experienced that this function works great for returning smaller amounts of data from Access databases. I say smaller amounts, but you can also return relatively larger amounts as well. But be warned, as with every Query process, the larger the data, the longer it takes to return the data.

I use this function to create "VLOOKUP()'s" from Access database. It has also come in really handy when creating reference tables for pivot tables and charts as well!


Enjoy, and have fun!


I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I can't believe that no one is interested in this?!!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[DrBowes]

I will definitely have a look at this as soon as I get a chance.....

Thanks

 

[Mightyginger]

Yes, this does indeed look very interesting - I will try it when I have a minute but one quick question - if the workbook is on autocalculate will this then reread in the data from the database automatically?

At the moment I have one computer on a network which writes prices to an access database, other computers can then read in this data in from this database. There are quite a few ways of skinning this particular cat but at the moment I use the external data source function in excel and refresh the data every minute. But, if I used this function would I even have to do that - would it automatically do this for me (and probably quicker too)?

 

[Bowers74]

You would only have to press F9 to update the data in the Excel sheet. But remember that this should really be used for returning small amounts of data to the Excel sheet (i.e. not too many different query types).

Good Luck!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!