Applies to: Excel 2007.
I've developed a pivottable that utilises an odbc connection in the first worksheet of my workbook. When I look at the connection parameter properties via data>connections>properties>parameters I have indicated 4 parameters for my query being:
start year,end year, start no, end no.
I have the following query text setup for the connection:
SELECT fsc_plcosts.pla_num, fsc_plcosts.pla_dsc, fsc_plcosts.type, fsc_plcosts.code, fsc_plcosts.code_desc, fsc_plcosts.pst_yer, fsc_plcosts.act_p12, fsc_plcosts.life_expense, fsc_plcosts.life_revenue, fsc_plcosts.life_profit_loss, fsc_plcosts.life_perf
FROM authlive.dbo.fsc_plcosts fsc_plcosts
WHERE (fsc_plcosts.pst_yer Between ? And ?) AND (fsc_plcosts.pla_num Between ? And ?)
My question is this. All the research I've done on the setparam method links this to the querytables object. If I do the following in the immediate window
activeworkbook.sheets(1).querytables.count
I get 0.
How can I set a parameter for a querytable that doesn't exist? I know the pivotable querytext is actually saved under the pivotcaches object.
I get activeworkbook.pivotcaches.count = 1.
I can't seem to use the setparam method to alter this though.
Can anyone assist? I have spent several hours looking at different posts but all refer to querytables object in their solutions.
Please note that all of this is needed because for some reason when I set the parameters to a cell in the worksheet it doesn't save this information upon closing(ie. when I open it again the cell references are blank).This also means that if I try to save the workbook again without manually entering the links Excel crashes. I have posted on microsoft answers in relation to this issue.
Any help would be greatly appreciated.
Regards,
W00DY
I've developed a pivottable that utilises an odbc connection in the first worksheet of my workbook. When I look at the connection parameter properties via data>connections>properties>parameters I have indicated 4 parameters for my query being:
start year,end year, start no, end no.
I have the following query text setup for the connection:
SELECT fsc_plcosts.pla_num, fsc_plcosts.pla_dsc, fsc_plcosts.type, fsc_plcosts.code, fsc_plcosts.code_desc, fsc_plcosts.pst_yer, fsc_plcosts.act_p12, fsc_plcosts.life_expense, fsc_plcosts.life_revenue, fsc_plcosts.life_profit_loss, fsc_plcosts.life_perf
FROM authlive.dbo.fsc_plcosts fsc_plcosts
WHERE (fsc_plcosts.pst_yer Between ? And ?) AND (fsc_plcosts.pla_num Between ? And ?)
My question is this. All the research I've done on the setparam method links this to the querytables object. If I do the following in the immediate window
activeworkbook.sheets(1).querytables.count
I get 0.
How can I set a parameter for a querytable that doesn't exist? I know the pivotable querytext is actually saved under the pivotcaches object.
I get activeworkbook.pivotcaches.count = 1.
I can't seem to use the setparam method to alter this though.
Can anyone assist? I have spent several hours looking at different posts but all refer to querytables object in their solutions.
Please note that all of this is needed because for some reason when I set the parameters to a cell in the worksheet it doesn't save this information upon closing(ie. when I open it again the cell references are blank).This also means that if I try to save the workbook again without manually entering the links Excel crashes. I have posted on microsoft answers in relation to this issue.
Any help would be greatly appreciated.
Regards,
W00DY
