AverageCoder.Net - Programming - Software - Techie Stuffs

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

Posted by Daniel - 8,039 Views

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

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

ODBC SETTING

1. Create an ODBC Connection to your DB

EXCEL PART

1. Navigate to Data -> Import External Data -> New Database Query
2. On the Database Tab, select the ODBC name you had created
3. Click OK
4. Click Cancel (just do it)
5. Select Yes if Microsoft Query ask you to edit the query
6. Click Close on Add Tables dialog (just do it)
7. Click SQL button on the toolbar
8. Enter {Call MyQuery(?, ?, ?)}
9. Click OK <- You will get an error message, Click OK to continue anyway
10. Enter each of your parameter
11. Close Microsoft Query
12. Insert The imported data on your worksheet
13. 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):

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

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

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:

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.

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

1. 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 … […]

2. Jason Gaved Says:
   October 27th, 2008 at 6:57 am

   Good stuff, I liked the article ;)

3. Gracie Says:
   June 11th, 2009 at 9:37 pm

   As a Newbie, I am always searching online for articles that can help me. Thank you

4. Scott Says:
   September 15th, 2009 at 1:58 am

   Intresting, I am glad you done your research as I had no idea for a fix for this.

   Thanks

   Scott’s last blog post: Excel Training Courses

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website

Options for your comment:

Notify me via e-mail if anyone post more comment to this post

Enable CommentLuv which will try to get your last blog post to be inserted at the end of your comment