locked
Excel 2016 Professional Plus - PowerPivot Data Tables disappeared after closing and re-opening saved workbook RRS feed

  • Question


  • Team, we clients need your help in order to know if Microsoft has already a solution for this frustranting BUG in Excel 2016.

    The problem is:

    All tables connected in Data Model simply disappear when close application using upper right close (x) button and then save the file. When you re-open the file, no more Data connection nor Data Model. You have to start over from 0. How is possible Microsoft has this issue in Microsoft Excel 2016? This subject is not new as you can see according to link https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bbdf4f0a-c928-4c78-a286-58b82b7dbaec/powerpivot-data-tables-disappeared-after-closing-and-reopening-saved-workbook?forum=sqlkjpowerpivotforexcel

    The workaround to not have your tables gone after close file is save and close the file using Menu File > Save or Save As > File > Close, but this is not the solution. If any user accidentally close file using upper right close (x) button we loose everything!!

    Error message:
    Sorry, PowerPivot can't open the Data Model because there was a COM exception while opening. You might be opening a workbook on a corrupt instalation of Excel. CLick Details for more information.

    Error Detail:

    ============================
    Error Message:
    ============================

    Referência de objeto não definida para uma instância de um objeto.

    ============================
    Call Stack:
    ============================

       em Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.HandleRegionalSettingsChange()
       em Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSandboxAfterConnection(String errorCache)
       em Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSafeSandboxAfterConnection(String errorCache)
       em Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent, String errorCache)

    ============================


    Detail of Software I am using:

    Microsoft Office Professional Plus 2016
    2016 MSO (16.0.4266.1001) 64 Bits

    Detail of Hardware I am using:

    Microsoft Office Professional Plus 2016
    2016 MSO (16.0.4266.1001) 64 Bits

    Windows 10 Pro 64 Bits
    Chip: Intel(R) Core(TM) i5-4310U CPU 2.00GHz / 2.60 GHz

    Monday, February 6, 2017 10:19 PM

Answers

  • This is the only fix I am aware of. It is quite old now, but I guess maybe your version doesn't have it. https://support.microsoft.com/en-us/help/3114297/update-fixes-issues-that-affect-power-pivot-and-the-analysis-services-engine-in-excel-2016

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Tuesday, February 7, 2017 8:41 PM
    Answerer

All replies


  • Team, we clients need your help in order to know if Microsoft has already a solution for this frustranting BUG in Excel 2016.

    The problem is:

    All tables connected in Data Model simply disappear when close application using upper right close (x) button and then save the file. When you re-open the file, no more Data connection nor Data Model. You have to start over from 0. How is possible Microsoft has this issue in Microsoft Excel 2016? This subject is not new as you can see according to link https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bbdf4f0a-c928-4c78-a286-58b82b7dbaec/powerpivot-data-tables-disappeared-after-closing-and-reopening-saved-workbook?forum=sqlkjpowerpivotforexcel

    The workaround to not have your tables gone after close file is save and close the file using Menu File > Save or Save As > File > Close, but this is not the solution. If any user accidentally close file using upper right close (x) button we loose everything!!

    Error message:
    Sorry, PowerPivot can't open the Data Model because there was a COM exception while opening. You might be opening a workbook on a corrupt instalation of Excel. CLick Details for more information.

    Error Detail:

    ============================
    Error Message:
    ============================

    Referência de objeto não definida para uma instância de um objeto.

    ============================
    Call Stack:
    ============================

       em Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.HandleRegionalSettingsChange()
       em Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSandboxAfterConnection(String errorCache)
       em Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSafeSandboxAfterConnection(String errorCache)
       em Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent, String errorCache)

    ============================


    Detail of Software I am using:

    Microsoft Office Professional Plus 2016
    2016 MSO (16.0.4266.1001) 64 Bits

    Detail of Hardware I am using:

    Microsoft Office Professional Plus 2016
    2016 MSO (16.0.4266.1001) 64 Bits

    Windows 10 Pro 64 Bits
    Chip: Intel(R) Core(TM) i5-4310U CPU 2.00GHz / 2.60 GHz

    I Forgot to mention that you have to open "Manage Data Model", shortcut Alt > A > DM (using Excel 2016 in English). Then save work throught Power Pivot window and then try to close the file using upper right "close" button.
    Monday, February 6, 2017 10:24 PM
  • It is buggy - unfortunately.  There have been some patches released but none have solve all issues.  There is a remediation project underway now, but no delivery date has been provided.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Monday, February 6, 2017 11:22 PM
    Answerer
  • Matt, thanks for your reply. 

    Do you have a workaround for this instead of Menu File > Save or Save As > File > Close? I mean, do you think this is the only thing we could do in the meanwhile? 

    Tuesday, February 7, 2017 2:12 AM
  • This is the only fix I am aware of. It is quite old now, but I guess maybe your version doesn't have it. https://support.microsoft.com/en-us/help/3114297/update-fixes-issues-that-affect-power-pivot-and-the-analysis-services-engine-in-excel-2016

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Tuesday, February 7, 2017 8:41 PM
    Answerer
  • Anyone from the Microsoft-team here who could shed some light please?

    Imke Feldmann TheBIccountant.com

    Sunday, February 12, 2017 8:35 AM
    Answerer
  • Still waiting.

    Jimmy_PQ

    Sunday, March 12, 2017 10:57 AM