I have a sheet which uses a data connection to obtain data for a pivot table from an external database using a SQL query. The user can set parameters for the SQL by completing a few cells in the worksheet. The process is described here.

When I save and reload the file and refresh the data Excel crashes. I have found that the contents of the 'Get the value from the following cell:' field from the Parameters dialog box are not saved.

I have tried Excel 2007 and Excel 2010, saving in both XLSX and Binary formats but none of them save the setting. Does anyone have a fix or a workaround?

Screenshot of the dialog box is here:

  • Are these cells which the parameters are taken from initially holding any values by default? It may be that if they are empty to start the query fails? Is that a possible cause?
    – datatoo
    Feb 19 '11 at 20:25

This problem comes when we want to 'import the data' directly into a 'PivotTable Report'.

As a workaround'import the data' as 'Table' first in a separate sheet(say 'DB Data' sheet). Then build a pivot using this data in a separate sheet (say 'DB Pivot' sheet).

The parameters for the query (startdate etc.,) can be referenced from the 'DB Pivot' sheet. However, when the parameters change you have to refresh the table in 'DB Data' first and then the pivot.

To do it in with a single click, include a commandbutton(or a macro) with the following code.

Sheets("DB Data").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.