Excel crashing when using Cell reference for parameter for an SQL execution

Question

In an Excel 2010 workbook, I have a pivot table based on a very simple query that gets data from a My SQL database (SQL: SELECT Score FROM tblScores WHERE Year = ?). I have edited the Year parameter in connection properties to get the parameter value from a cell (Change Data Source > Connection Properties > Definition > Parameters - select 'Get the value from the following cell' and input =Sheet1!J1)  The pivot table refreshes perfectly using the parameter value in J1, but if I save the file and reopen it, Excel crashes when I refresh the pivot table. For some reason, the above setting (to get the parameter value from cell J1) is not saved with the workbook. I've seen some posts on the net with a similar problem, but no solutions - has anyone found a working solution to this?

Answer 1

Since you’re using a pivot table that fetches data from an SQL database, it would be best that you post your query in the Excel IT Pro forum mentioned below, for better suggestions.

http://social.technet.microsoft.com/Forums/en/excel/threads