Microsoft Excel Import External Data Problem: When Microsoft Query doesn't recognize some of your query parameters

Microsoft Excel Import External Data Problem: When Microsoft Query doesn’t recognize some of your parameters

Posted by Daniel - 339 Views

programming article This is a summary pulled from a discussion on IT-Toolbox with Alejandro Rodriguez titled “Excel query doesn’t allow me create 3rd paremeter for a criteria“. He said there he was having a problem with Mirosoft Excel’s Import External Data feature. He posted on the forum that Microsoft Query is not recognizing one of his query parameters.

I got curious about it and then I tried a simple test against Microsoft Query via Excel Import External Data feature, and Yes! Microsoft Query is not recognizing some of my test query parameters. If it does recognize the1st and the 2nd parameters, why it does not recognize the 3rd? I have no idea why, but I guess it’s a bug and they forget to debug it - so be it :D

Microsoft Query allows you to enter a hard coded SQL statement with parameters directly into the editor but it didn’t work as I described previously so we need to figure out another way to get around it.

As we know that most Database servers allow you to create a stored SQL statement for future use such as “query” on MS-Access and “stored procedure” on SQLServer. This ability gives me an idea:

What if I create an MS-Access query which can accept parameters and then call it from Excel’s Import External Data feature? Can Microsoft Query recognize all the parameters with this kind of method? The answer is Yes! Microsoft Query is recognizing all of the parameters.

Following are the steps on how to call stored procedure to Import External Data into Microsoft Excel. Some steps may look strange but based on my tests, each of them needs to be taken correctly,

___________________________________________________________________________

MS-ACCESS PART

Open the your Access DB
Create a Query by using SQL mode
Enter your SQL statement there (inluding the ? sign)
Save it as “MyQuery”

ODBC SETTING

Create an ODBC Connection to your DB

EXCEL PART

Navigate to Data -> Import External Data -> New Database Query
On the Database Tab, select the ODBC name you had created
Click OK
Click Cancel (just do it)
Select Yes if Microsoft Query ask you to edit the query
Click Close on Add Tables dialog (just do it)
Click SQL button on the toolbar
Enter {Call MyQuery(?, ?, ?)}
Click OK <- You will get an error message, Click OK to continue anyway
Enter each of your parameter
Close Microsoft Query
Insert The imported data on your worksheet
Select any cell on the area where you put the imported data then go to Data -> Refresh Data

___________________________________________________________________________

The question mark there is a sign that it can accept parameter. Let me give you an example to ease the understanding just in case this kind of stuff is a new one for you. So if you type an SQL statement like the one below into the MS-Access Query Designer (SQL Mode):

SELECT [productid],[productname],[productquantity] FROM [products] WHERE [productquantity]>=?;

and then you save the query as “MyQuery“, when you call the query using the following syntax from Microsoft Query (SQL Mode):

{Call MyQuery(100)}

That call will pass the parameter value (100) to “MyQuery” and in the background it will actually generate an SQL statement like the following,

SELECT [productid],[productname],[productquantity] FROM [products] WHERE [productquantity]>=100;

That’s how it works, the question mark is replaced by the parameter value passed to MS-Access saved query.

TIPS IF YOU USE SQLSERVER INSTEAD OF MS-ACCESS

If you use SQLServer instead of MS-Access to Import External Data into MS-Excel, the method is the same but on SQLServer we call the saved query as “stored procedure” and here is the syntax to create it based on the example above:

CREATE PROCEDURE MyQuery
@par_productquantity int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT [productid],[productname],[productquantity] FROM [products] WHERE [productquantity]>=@par_productquantity;
END

So, if you are having the same problem with hard coded SQL Statement when you need to import external data into Microsoft Excel, you can try this method.

Good luck :) and don’t forget to check the discussion thread related to this issue on IT-Toolbox for more detail.

__________________________

The following posts are programmatically considered as related to the current post by YARPP Plugin:

Related posts brought to you by Yet Another Related Posts Plugin.

share this article

This post as PDFPosted in: Programming - October 2008

Latest from Programming

2 Responses to “Microsoft Excel Import External Data Problem: When Microsoft Query doesn’t recognize some of your parameters”

Biggest Halloween » Blog Archive » Microsoft Excel Import External Data Problem: When Microsoft Query … Says:
October 19th, 2008 at 4:09 am
[…] Daniel wrote an interesting post today onMicrosoft Excel Import External Data Problem: When Microsoft Query …Here’s a quick excerptThis is a summary pulled from a discussion on IT-Toolbox with Alejandro Rodriguez titlted “Excel query doesn’t allow me create 3rd paremeter for a criteria“. He said there he was having a problem with Mirosoft Excel’s Import External … […]
Jason Gaved Says:
October 27th, 2008 at 6:57 am
Good stuff, I liked the article ;)

Get my Full Feed Here or you can subscribe to one of my category based feeds below:

Latest Blog Entries

Neighbours and Friends

Login Here

username
password

remember me
Register
Recover password

Comments - Thanks Guys :)

Dennis Wurster: Will your modifications allow me to create maps with multiple points on them? Dennis Wurster’s last blog post: PayPal...
Coarveadvexon: As the call, so the echo :D Coarveadvexon’s last blog post: Ford Kuga 2.5 Turbo
Chat: nice thank you very much for this article.
a_suliman: thanx for every thing
Miss Lunatic: Jaja, it’s a yolk. Thanks for the compliment ;) Miss Lunatic’s last blog post: Pitas y guacamole