I may have to figure out using VBA - as soon as I reference the dates on the separate tabs in the query an add it to our Sharepoint site - the refresh crashes Excel. Take the original file and try and open it (locally on my machine) AFTER trying to refresh the workbook when it crashes from Sharepoint - it crashes.
So the steps:
1) Create Excel workbook (having added parameters via the Dates tab).
2) Experiment changing dates and refreshing work sheets - all is ok.
3) Upload file to Sharepoint.
4) Open from Sharepoint FOR Edit and change dates and refresh the data - error below.
5) Go back to the original file on my C drive, open it, and try and refresh the data (Data --> Refresh All --> Refresh specific work sheet - Excel crashes with same error.
EDIT: Turns out that when saved locally - the parameter references are lost. This is likely causing the crashes but I have no idea why the parameter references are being removed. I have the radio button for "Get the values from the following cell" active and can set the reference any way I can. As soon as I save it - the references are gone.quote:
Application Version: 14.0.7132.5000
Application Timestamp: 53ec9d06
Fault Module Name: EXCEL.EXE
Fault Module Version: 14.0.7132.5000
Fault Module Timestamp: 53ec9d06
Exception Code: c0000005
Exception Offset: 00817e23
If I click on Debug the next dialog box lists - "An unhandled win32 exception occurred in EXCEL.EXE[9444] (the number in block seems to change every time).
When Visual Studio starts the nest dialog box is: "Microsoft Visual Studio C Runtime Library has detected a fatal error in EXCEL.EXE." Nothing shows in VS - it just opens to an empty solution.
I have the connection set up to use Windows Authentication like we normally do when connecting via SQL Query Manager (2008 R2).