Cell Reference parameters used in Excel SQL Queries

[ramon.aog]

Hi,

[FONT=&quot]I am extracting data from an SQL database into Excel 2007 using MS Query.

I've used the following SQL code to prompt for the parameter:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]"WHERE
casosdeuda.ConceptoDeuda LIKE (3) and
a.`TipoCaso` in (?,?) and
a.`TipoDeuda` in (?,?)"


Using Data/Connections/Properties/Definitions/Parameters, I am using "Get the value from the following cell" to plug in the parameter. This works perfectly until I save and close the file.

When the file is opened I need to link up the cell references again. How do i save the cell reference parameters so they don't have to be entered each time the file is opened??[/FONT]
[FONT=&quot] Can anyone help?[/FONT]
[FONT=&quot]thanks
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]-alex
[/FONT]

 

[nicovega]

Solved it. I found there is a bug when using pivot tables instead of table to import external data.

the way to solve it: Create a new empty file, import external data using the existent MS query file and choose Table instead of pivot table.
After doing this, configure the parameters in order to pick dates or whatever from a cell in the file, and save it.

Now its working fine, I've create an excel with a macro that updates a lot of other excel files (using msquery and parameters) and send them through email (lotus notes) like an "alerts".

 

[zonian]

Solved it. I found there is a bug when using pivot tables instead of table to import external data.

the way to solve it: Create a new empty file, import external data using the existent MS query file and choose Table instead of pivot table.
After doing this, configure the parameters in order to pick dates or whatever from a cell in the file, and save it.

Now its working fine, I've create an excel with a macro that updates a lot of other excel files (using msquery and parameters) and send them through email (lotus notes) like an "alerts".

Hello,

This solved your problem to get data, but not the problem as to why parameters are lost when using a pivot table. After all these years, this problem still exists. And the world is lesser for it.