title: VSTO-Addin - Calling Excel-Connection "breaks" Powerpivot
body: <p>I’m a struggling with an Excel automation problem. I was hoping someone can help me with that - any help is highly appreciated.</p>
  
  <p>The setup is as follows: I’ve created a VSTO add-in (vb.net) that works for Excel 2010 and 2013. It supports typical BI tasks and makes use of WorkbookConnections. More precisely, it operates on a certain WorkbookConnection that the user has to select for a Workbook (in case there are more than one). When the user opens the Workbook, it reads some data model information from the connection using ADO.</p>
  
  <p>With Excel 2013, a strange thing occurs if the add-in is operated on the built-in connection to the Excel (PowerPivot) Model (“ThisWorkbookDataModel”): The add-in works fine, but as soon as you try to open the PowerPivot window, a COM exception comes up (see bottom of Email). Afterwards, an exception is raised every time the Connection is used within the add-in, so you have to close and re-open Excel.</p>
  
  <p>I’ve narrowed the problem down to this: Any time the Model gets loaded because of add-in code (e.g. calling “Globals.ThisAddin.Application.ActiveWorkbook.Model.Initialize()”, but also if you just loop through the list of WorkbookConnections), the connection somewhat breaks for PowerPivot with the above mentioned effect. Additionally, my testing revealed the following:</p>
  
  <ul>
  <li><p>This does not happen if you do the same thing with a VBA macro</p></li>
  <li><p>But if you call that macro from VSTO using “Globals.ThisAddIn.Application.Run(theMacro)”, it does happen</p></li>
  <li><p>Only if you call the macro using the “SendKeys()”-command, the connection remains intact</p></li>
  </ul>
  
  <p>Has anyone any idea what may cause this problem? Or, as a workaround, how it’s possible to initiate “good” Model initialization from the add-in similar to what is done by calling a VBA macro via SendKeys()? Obviously, programmatically adding a macro to the workbook and calling it with SendKeys is not really something you want to sell to customers… ;-)</p>
  
  <p>Thanks and best regards!</p>
  
  <p>PS: The COM exception when opening the PowerPivot window:</p>
  
  <p>============================</p>
  
  <p>Error message:</p>
  
  <p>============================</p>
  
  <p>Object reference not set to an instance of an object.</p>
  
  <p>============================</p>
  
  <p>Call stack:</p>
  
  <p>============================</p>
  
  <p>at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSandboxAfterConnection(String errorCache)</p>
  
  <p>at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSafeSandboxAfterConnection(String errorCache)</p>
  
  <p>at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent, String errorCache)</p>
  
  <p>============================</p>