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 - 77,127 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.