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]

 

[ramon.aog]

Any ideas?

 

[xenou]

There is a checkbox for saving the reference for future queries. I'm assuming you've checked that. It seems like you're query should be saved. I'd try starting from scratch with a new workbook and seeing if you have better luck the second time around.

Alex

 

[ramon.aog]

There is a checkbox for saving the reference for future queries. I'm assuming you've checked that. It seems like you're query should be saved. I'd try starting from scratch with a new workbook and seeing if you have better luck the second time around.

Alex

I did use the checkbox, and the query is saved. The reference for the parameters however doesn't appear once I reopen the document. So if you press refresh to the pivot table excel crashes because the reference for the parameters are lost.

i don't know what else to do

 

[Norie]

alex

Where exactly are you using that SQL string?

What happens when you don't specify cell references for the parameters?

 

[ramon.aog]

alex

Where exactly are you using that SQL string?

What happens when you don't specify cell references for the parameters?

I made the script to pull some data for a report, I already have the pivot table made, so what i have to do is just to refresh the pivto table (and it runs the query again)

I can input the paramaters mannually every time i refresh it, but i want to make the report "ser friendly" and have the user select the product he wants to see.

So i have the different products on a drop down list and a reference number next to it (which is the number that i am using as a parameter to filter the data base)

If i use the method described above, where i fix the parameter to a reference cell it works during the time i have the report opened. when i save, close and re-open the report
i click refresh and excel crashes becasue the references are lost, so basically its using "blank" as a parameter.

so i would have to re link the whole thing again for it to work.

So basically everything is working fine, the problem is the fixed cell reference for the parameters aren't saved :/

 

[Norie]

ramon

What code if any are you using?

You mention an SQL database, do you mean SQL Server, mySQL, ORACLE...?

 

[ramon.aog]

ramon

What code if any are you using?

You mention an SQL database, do you mean SQL Server, mySQL, ORACLE...?

mySQL

code:

SELECT
a.CasoID,
tipocaso.Descripcion AS `Portfolio`,
tipodeuda.Descripcion AS `Debt Type`,
casosdeuda.Monto AS `Orig Principal`,
last_day(a.FechaCartera - interval 1 month) + interval 1 day as 'Choff Vintage',
a.FechaCartera AS `Choff Date`,
gral_other.GralTraduccion AS `Purchase Date`,
ROUND((DATEDIFF(gral_other.GralTraduccion, a.FechaCartera) / 30)) AS Include,
opc_new_master_agent.Channel
FROM
casos a
LEFT OUTER JOIN tipocaso ON (a.TipoCaso = tipocaso.TipoCasoID)
LEFT OUTER JOIN tipodeuda ON (a.TipoDeuda = tipodeuda.TipoCasoID)
LEFT OUTER JOIN casosdeuda ON (a.CasoID = casosdeuda.Caso)
LEFT OUTER JOIN gral_other ON (a.TipoCaso = gral_other.GralID)
LEFT OUTER JOIN empresa ON (a.Empresa = empresa.EmpresaID)
LEFT OUTER JOIN opc_new_master_agent ON (a.Empresa = opc_new_master_agent.EmpresaID)


WHERE
casosdeuda.ConceptoDeuda LIKE (3) and
`opc_new_master_agent`.`Channel` not like "Legal" and
a.`TipoCaso` in (?,?,?,?,?,?) and
a.`TipoDeuda` in (?,?,?,?,?,?)

 

[xenou]

It is sometimes possible to edit the query with VBA at runtime ... you might try 1) starting from scratch 2) turn on the macro recorder as you create and run your query 3) inspect the code produced by the macro recorder 4) and then edit it to allow for the addition of variables based on cell values. That's a workaround but it might get you through this - as far as I can tell your setting really should be saved here.

 

[nicovega]

Ramon, I've read your post and it seems to be the same issue I have in my workbook.

Have you found the solution to this issue? I'm almost getting crazy..

I'm working with excel 2007, and ODBC connection to Oracle Database, through Microsoft Query. I'm picking the parameter from a cell value, but once i close the file and open it again, the parameter config is in blank.

thanks in advance!