Pivot Table: Problem with data connection parameter

L

Legacy 122759

Guest
Hello all,

I'm having an issue with a data connection in Excel 2007. I've not been able to find a solution anywhere online so I'd be tres grateful if anyone could shed some light on this. SQL Server is involved but I believe the problem is with Excel so apologies if people think I'm posting this in the wrong place.

I'm attempting to set up a very simple reporting front end for a SQL Server 2008 database. I'll try my best to explain the setup.

I have a view within a SQL Server database that is filtered by a stored procedure. The stored procedure requires two parameters to be fed into it. I've created a spreadsheet that contains two cells, both with validation lists so that the user can select preset values to use as parameters for the stored procedure. SQL Server then spits out data as required into both a table and a pivot table. There is a little bit of VBA attached to the worksheet containing the Parameter drop downs that refreshes the entire workbook when it detects a change. This is to refresh the pivot table with the 'new' data from SQL server.

The table is working fine. The pivot table however, isn't. The pivot table works fine until I close the workbook and reopen it. When I do this, Excel appears to have lost the cell reference for the cells containing the required parameters and requests me to supply them manually. As I mentioned earlier, the table does not lose the references, it works fine. This is why I believe the problem is in Excel, possibly something to do with how pivot tables deal with data connections maybe?

I do not believe the VBA is contributing any problems. This is because I have macro security set to medium. Excel requests me to supply the location of parameter values even if I choose not to allow code to run.

The command text I'm using in the connection properties (for both the table and the pivot table) is as follows:
exec usp_Plan_Report_Filter ?,?
Then in the parameters section of Connection properties I have Parameter 1 and Parameter 2 set as "Get the value from the following cell" and "Refresh automatically when cell value changes" is checked.

Just to reiterate, everything works fine until I close and then reopen the workbook.

Any help would be much appreciated.
 
L

Legacy 122759

Guest
The only workaround I've found is to base a pivot table on a table that is populated by the data connection. Not ideal as it requires additional code to refresh the pivot table once the table has refreshed and doesn't appear to be as stable as I would like (nor as elegant).

I've also discovered an interesting quirk. Although I'm using Office 2007, the report I was working on was saved to .xls format. If I recreate the spreadsheet but save to .xlsx format and reopen it, the spreadsheet corrupts when it tries to refresh itself.

I've already resigned myself to having to use some VB to do this. It's driving me up the wall.

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.worksheet.functions&mid=60c756e6-2c2b-473a-a4ab-fd2c4ba3e13f&sloc=en-us
 

andrepws

New Member
Joined
Sep 15, 2009
Messages
3
This little bug of xls to xlsx I got the same. I´m using Office 2010 too and it has the same problem. I´m thinking to change the procedure...
I posted this problem on Microsoft´s Excel 2010 blog too... any news, I´ll tell you...
 

Palendrone

Board Regular
Joined
Jul 30, 2004
Messages
208
In the same boat here myself except that I have probably spent 30 hours trying to find out why my .xlsx file kept crashing and corrupting on saving the edited file - this was happening as it was trying to save the file with missing links in the parameters cell link box, a swine to figure out when you have been experiencing the file crashing becuase of a complex graph!!! Arrrrghhhh is it my imaginiation or was 2003 much more stable?

Hope they fix this soon as it's doing my head in too!!!!
 

Palendrone

Board Regular
Joined
Jul 30, 2004
Messages
208
Getting a little bored after only two weeks of opening a file and having to manually re-enter the parameter cell, is there any VBA that I can use for this on workbook open?

I did try "insucessfully" recording a macro which makes me think its not possible but any help well received :0)

Michael
 
L

Legacy 122759

Guest
I'm glad it's not just me.

Is there any way of raising issues like this with Microsoft? It feels like it's a bug rather than it being me trying to make something work that shouldn't work. It's just a bug that hardly anyone ever encounters.

I've said this before but if it worked it would be such an elegant and simple solution to so many problems. Stored procedures are so powerful, being able to feed them parameters on the fly felt like it was too good to be true, and it was!
 

Palendrone

Board Regular
Joined
Jul 30, 2004
Messages
208
So Office 2010 a port of the previous version with all the same bugs... Another worthwhile upgrade!

I'm gonna pester tech support soon over this as I am banging my head against a wall daily updating a report I have to manually enter parameters.
 

Forum statistics

Threads
1,084,733
Messages
5,379,498
Members
401,607
Latest member
Zemexi

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top