CODEWITHSUNDEEP
excelvb.netvbavstoexcel.application
Edson Mota
Edson Mota

Reputation: 11

How to load powerpivot data model with multiple tables?

I need to create an Excel application based on some tables.

I ran the code in VBA and it worked. In "Visual Studio" it doesn't work.

My code here:

Dim xlApp As Excel.Application
xlApp = Globals.ThisAddIn.Application
xlApp.ActiveWorkbook.Connections.Add2("Target Connection Name", "", _
     "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=SERVER\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet 
      Size=8192;Workstation ID=NAME;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=MYSQLDATABASE;", _
      """TAB_CATEGORIA"",""TAB_MOVIMENTO""", Excel.XlCmdType.xlCmdTableCollection, vbTrue, vbTrue)

When I execute this code, I receive the following message:

"The cartridge directory could not be found. Use the Windows Installer to repair the application."

I realized that when we generate a new instance, it works: 

Dim xlApp As NEW Excel.Application

But, unfortunately, I need the current instance 

xlApp = Globals.ThisAddIn.Application

When I use "Globals.ThisAddIn.Application" to generate a WorkbookConnection object, the exception is thrown.

More information,

  • Office 2013 updated
  • Visual Studio 2015 updated

**** You can see just below my stack trace *****

System.Runtime.InteropServices.COMException was unhandled by user code
  ErrorCode=-2146827284
  HelpLink=xlmain11.chm
  HResult=-2146827284
  Message=Não pudemos obter dados do Modelo de Dados. Veja a mensagem de erro que recebemos:

The cartridge directory could not be found. Use the Windows Installer to repair the application. 
  Source=Microsoft Excel
  StackTrace:
       em System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
       em Microsoft.Office.Interop.Excel.Connections.Add2(String Name, String Description, Object ConnectionString, Object CommandText, Object lCmdtype, Object CreateModelConnection, Object ImportRelationships)
       em ExcelAddIn2.Conectar.Button1_Click(Object sender, RibbonControlEventArgs e) na D:\work\projetos\SPHERA\OmieDecisionMaker\OmieDecisionMaker\ExcelAddIn2\Ribbon1.vb:linha 37
       em Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ControlActionRaise(IRibbonControl control)
       em Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ButtonClickCallback(RibbonComponentImpl component, Object[] args)
       em Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.Invoke(RibbonComponentCallback callback, Object[] args)
       em Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.System.Reflection.IReflect.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
  InnerException:

Upvotes: 0

Views: 457

Answers (1)

Edson Mota
Edson Mota

Reputation: 11

It looks like there's an incompatibility, regards the office 2016 be a single thread application. It makes the Add2 command be blocked from Excel UI

You can see the full answer here: https://social.msdn.microsoft.com/Forums/en-US/ddf2ab36-14a5-431c-b465-114aee6eb5d6/how-to-load-powerpivot-data-model-with-multiple-tables-vsto-excel-application?forum=vsto

Thank you!

Upvotes: 0

Related Questions