Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

share|improve this question
    
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

1 Answer 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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