Pivot Row Label Field Missing After Conversion

meridythe
meridythe used Ask the Experts™
on
I have an excel workbook (MSO 2003) that I have converted to an xlsm file in 2010, that contains pivot tables sourced from an access database.  I have converted the access database (also 2003) to an accdb file in 2010 and need to update the pivot table connection sources to point to this newly converted db.  Everytime I go in to update the pivot table connection source, the pivot table refreshes without the field that should be in the row label section.  I can easily add the field back into the table, but I am wondering if anyone can explain why this might be happening?  I have performed this conversion and connection updating on other spreadsheets and the pivot tables all seem to be fine.  Any ideas??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I am assuming that your "conversion" has much in common to the process of using worksheet data to generate a pivot table report.  If that is the case, then there could be any of several reasons, most which relate to the worksheet used as the data source.  Clink the link below.  When the web page comes up, click the hyperlink "Working with source data"; then go to the section on worksheet data.  There are tips for handling different types of worksheet data before you generate a Pivot Table report from the worksheet data.
          http://office.microsoft.com/en-us/excel-help/overview-of-pivottable-and-pivotchart-reports-HP010177384.aspx#BMworking_with_pivottable_and_pivotchar

Author

Commented:
Thanks for the reply puppydogbuddy, but my pivot table is linked to a query in an access db ("I have an excel workbook (MSO 2003) that I have converted to an xlsm file in 2010, that contains pivot tables sourced from an access database").   All I am doing is updating the connection sources that the pivot tables are using, to point to the accdb file instead of the mdb.  I am not sourcing my pivot tables from worksheet data.
OK, thanks for the clarificiation.  

Firstly, just want to be sure the caution statement below taken from  this reference is not related to the missing field in the row label section of the Pivot Table.  
              http://technet.microsoft.com/en-us/library/cc179188(office.12).aspx 
Caution: Users should consider existing links between workbooks before your organization converts existing workbooks to the Open XML Formats. Because earlier versions of Office Excel cannot update links to workbooks that are saved in the Open XML Formats, all linked workbooks should be simultaneously converted.  see this link for a codeapproach:   http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/167/for-each-loop-container-looping-through-folder-with-excel-files
__________________________________________________________________________________
Secondly, see the notes below from this reference for the Excel Connections Manager Editor. Not sure Whether the xlsm file you converted to is considered to be an Excel data source in this context
                                  http://msdn.microsoft.com/en-us/library/ms186848(v=SQL.90).aspx

Note:  
While you use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2003 or earlier, you cannot use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2007. Also, you cannot use the Microsoft Jet 4.0 OLE DB Provider to connect to an Excel 2007 data source. To connect to an Excel 2007 data source, use an OLE DB connection manager, and for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then, on the All page of the Connection Manager dialog box, for Extended Properties, enter Excel 12.0.
___________________________________________________________________________________
Thirdly, you may need for to install the Data Connectivity driver at this link:
http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

 
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

meridythe,
Not sure what method you used to change the connection to your new Access 2010 file so you may have already done these steps.  Click inside your pivot table and press Alt-D-P which will bring you back to step 3 of 3 on the Excel 2003 Pivot Table wizard.  Press the Back button to get back to Step 2 of 3 where the Get Data button is.  Press Get Data and walk through the steps again to establish the link to the new Access Db via the MS Query interface.   I can't specifically say why your row variable is missing but if you re-establish the link you will see the Access table/view in MS-Query and can verify the correct fields are there.
On the field that is missing, is there a space at the beginning or end of the Pivot Table field that may not match exactly the field coming from Access?  I beleive you indicated it is in the grid and can be re-added after the update.
Jerry

Author

Commented:
Hi Jerry,
Thank you soooo much for that Alt-D-P trick!!!!!  That is awesome!!!  I have been missing that wizard :)

I think you might be on to something with the naming of the field.  It looks as if there are two fields with the same name wihtin the data sourcing this pivot, one called only UWDivReg and one with the query name prefixing the field (qryNM.UWDivReg).  I am thinking that this duplication is causing the issue, because after the file is converted and relinked, the 2010 version removes the query prefix and renames the second field to UWDivReg2.
Meridythe,
You're welcome and thanks for the points!  The Alt-D-P has been a live saver for me too on occasion.  I think you are right on the field names.  Glad to help...
Jerry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial