4

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:

Community
  • 1
Robert Bergs
  • 141

1 Answers1

1

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
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Hope this helps.

Spica
  • 11