Using SQL.REQUEST

The file UsingSqlRequest.zip demonstrates how to use the workbook function SQL.REQUEST in Excel to get data:

  • direct from an MS Access table,
  • from an open or closed MS Excel workbook,
  • from the source workbook.
Below is a short overview of the file:
 
It consists of two demonstrations requesting external data:
  • In Sheet1, using MS Access
  • In Sheet2, using MS Excel

and one demonstration on requesting data from the same workbook:

  • In Sheet3, using MS Excel

All demonstrate how to return the results to a single cell or an array.
 

No VBA used.
 
If you do not have Access on your machine, this probably won't work, so see the Excel example. If you do have Access, make sure you change the path to the Northwind database on your PC.
 
One of the major benefits of this method is that you can get values from closed workbooks. A regular question to Excel newsgroups is:
Q: Can I use the INDIRECT function to reference a value in an unopened spreadsheet?
A: No, you can't use INDIRECT, but you can use SQL.REQUEST.
 
Q: How can I query using, say, a name rather than a number?
A: Enclose the name in single quotes
 
Q: I can't find the XLODBC add-in. I'm using Excel 2002
A: This add-in is no longer included with Microsoft Excel. You can get it from Microsoft by following this link - http://office.microsoft.com/downloads/2002/xlodbc.aspx
 
Q: I can't find the XLODBC add-in. I'm using Excel 2003. I've tried the above link but that says it can't find Excel 2002 or Excel XP.
A: This is where Uncle Bill isn't being too helpful :-(  But, here is a way to get to those files:
Open the xlodbc.exe using, say, Winzip.
Copy the files to a convenient location.
From within Excel, browse to the add-in file and load it as normal.
 
Q: It doesn't work in my non-English version.
A: You may need to change references such as 'Excel Files'. For example, in the Dutch version it becomes 'Excel-bestanden'.  (Thank you to Ruud van der Veen).
 

Published: 2000
Last updated: 01 March 2011 20:49