Excel 2016 bug - cannot switch QueryTable to take parameters from sheet after refreshing it with a constant value

Question

There appears to be a bug in Excel 2016.

If you have a QueryTable on a sheet, that query has a parameter, and at some point you refresh that query with a non-sheet value for the parameter, then you cannot switch the parameter to take its value from the sheet - Excel will crash. In order to make it work, you need to switch the QueryTable to take parameters from sheet, then save, then quit Excel completely, then reopen the file, and only then your query will work.

I believe it started after the latest Office update, and it is very annoying because it happens every time you create another query table. By default query tables are created with parameters prompted from the user each time, and they are refreshed when created, and the first thing you do after creating a querytable is repointing it to take its parameters from sheet cells, and when you do that Excel crashes and you lose your work and the table you just created.

The culprit appears to be the fact that the query table is refreshed with a non-sheet value as a parameter. As soon as that happens, it cannot refresh with a sheet value, it requires restarting Excel for that. If in the current working session you have not refreshed a query table with a non-sheet parameter, you can keep refreshing it with a sheet parameter, but as soon as you have, you need to restart Excel if you want to revert to taking the value from a sheet.

Steps to reproduce:

• Start with a fresh instance of Excel and a blank workbook.
• Create a query table (Data - From other sources - From Microsoft Query). Connect to a database using MS Query (in our case it's SQL Server 2016 via native client if that matters) and create a query against the database. Make sure it has a parameter.
• Return the query to Excel. Answer the dialog about where to place it. Excel will also ask what value to use as a parameter, and whether to store that value for future reference. Enter any value as an answer.
• You now have a query table on the sheet. Right click and go to Table - Parameters. Switch the parameter from "Prompt for value using the following string" or "Use the following value" to "Get the value from the following cell" and select a cell.
• Refresh the query. Observe that Excel crashes.

Alternative steps to reproduce:

• Start with a blank workbook and create a parametrized database query from MS Query like described above.
• Switch it to take the parameter value from a sheet cell, but do not refresh the query.
• Save the file and exit Excel. Open the file again, refresh the query, observe it is working fine and takes the value from the cell.
• Now go to Table - Parameters and switch the parameter mode to a non-sheet option ("Prompt for value using the following string" or "Use the following value"). Then refresh the query providing a fixed value.
• Now go to Table - Parameters and switch the parameter back to taking the value from a sheet cell. Click OK and Refresh the query. Observe that Excel crashes.

Work around:

Each time you create a query table and switch it to take parameters from a sheet cell, save the file, close and open it again, and do not touch the parameter mode after that, and if you do, save and restart again.

When Excel crashes, the information it gives is:

Problem signature:

  Problem Event Name:    BEX

  Application Name:    EXCEL.EXE

  Application Version:    16.0.9226.2114

  Application Timestamp:    5ade623f

  Fault Module Name:    unknown

  Fault Module Version:    0.0.0.0

  Fault Module Timestamp:    00000000

  Exception Offset:    1c418918

  Exception Code:    c0000005

  Exception Data:    00000008

  OS Version:    6.1.7601.2.1.0.256.48

  Locale ID:    2057

Answer 1

I'm having the same problem ! It's impossible to alternate beween constant value or cell based value in a parameterized query in this new version of Excel . The work around that you proposed is not something reasonable when using parameterized queris dinamically. The user loses degrees of freedom.

Answer 2

Hi Slepmog,

We appreciated your reply on reporting this issue with Excel 2016. We encourage that you post this issue to our Excel UserVoice forum so that our Excel Engineers can investigate this possible bug with Excel 2016. Kindly click on this link to access our Excel UserVoice forum.

Let us know whenever you need further assistance.