Parameter Query Problem in Excel 2007

[S-Martin]

Has anyone else had the problem I am having with a parameter query in Excel 2007? I set up a query in MS Query to an external database with the parameters being a start and end date. The results of the query are returned to a PivotTable in Excel. By editing the connection properties, I can change the parameters to be linked to specific cells on the same worksheet as the PivotTable that contain the start and end dates. The idea is that the user can change the start and end dates on the worksheet and then the PivotTable can be refreshed with the new information. This works fine until I close the workbook and then reopen it. It seems that Excel does not save the parameters I set in the previous session because Excel crashes when it tries to either run the query or save the workbook. If I go in to the connection properties and add in the parameters, it will work fine again.

I saw in another post on this forum by ramon.aog that he received the suggestion to edit the query in VBA at runtime to add the parameters. When I tried that, Excel would create a new connection for the PivotTable to be linked to, so even though the VBA code correctly updated the connection with the correct parameters, the PivotTable would still be wrong because it was not linked to the corrected data connection. Is this an Excel 2007 bug or is this behavior by design for some strange reason?

Finally, my programmer and I came up with a workaround that seems to work but it seems clumsy and we were wondering if there was a better way. What we did was add to the VBA code to cause the PivotTable to change its connection to a connection named Temporary, then modify the parameters in the original data connection, and finally change the PivotTable's connection back to the original connection as modified. As I said, this seems to work but it makes the update process take longer.

I would be glad for any input from anyone who has experienced this issue or who knows a better solution.

Thanks,
S-Martin

 

[doofusboy]

It seems that Excel does not save the parameters I set in the previous session

That piece can be resolved by you writing the parameters to a hidden cell/worksheet before the workbook is closed/saved and then accessing them in Workbook_Open.

 

[S-Martin]

doofusboy,

I'm not sure I fully understand what you are saying. Maybe I did not make myself clear in my earlier post.

What I have is a PivotTable that gets its data from an outside ODBC source. The query has parameters for a Start Date and End Date. The actual parameters are stored in cells A1 and A2 of the worksheet where the PivotTable appears. However, what I am trying to do is change the query parameters to reference these 2 cells, rather than containing "hard" data. I can open the data connection properties and change the query parameters to reference these 2 cells, but when I close the workbook and re-open, the parameters need to be re-entered. If you are saying that you have a way to overcome this problem, I would appreciate a more detailed response.

Thanks,
S-Martin