Guest Post by Dany Hoter

After I published a post about manipulating relationships, Rob suggested that I take a step back and cover the entire scope of what’s possible with the object model.

Can you build a model from scratch? Can you add a new table to an existing table? Can you add calculated columns? What about calculated measures? , Can you change a connection for an existing table in the model?

The short answer to these questions is Yes, Yes, No, No, Yes

The longer version is the rest of this post. Everything in this post is NOT possible in Excel 2010 – this stuff works in 2013 only.

The object model consists of the following elements:

clip_image001

The only property that I found useful in this list is ModelRelationships collection which I used extensively in the previous post.

The ModelTables collection looks promising as it contains ModelTableColumns and could be the way to introduce new tables, new columns or even measures into the model. Unfortunately all these collections are read-only and cannot be used for adding to the model.

So how is still possible to add new tables or even to start a model from scratch?

It all has to do with the method add2 of the Connections collection.

 

This method can be used to add new tables to the data model from a variety of sources.

This is the definition of this method:

image

The two last Boolean parameters are related to adding connections to the data model.

I started by recording macros while importing data into Excel but I must admit that a lot of cleaning was needed after recording Smile

If you use any of these examples on an empty workbook, a data model will be created, the table(s) will be added to it and if relevant also relationships between the imported tables will be added.

So if you run this specific example that imports data from an OData feed available on the web it should always work:

Sub TwotablesFromOData()

ActiveWorkbook.Connections.Add2 _

“DataFeed_2_services-odata-org Multiple Tables”, “”, _

“DATAFEED;Data Source=http://services.odata.org/northwind/northwind.svc/;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=SSPI;Keep Alive=true;Persist Security Info=false;Service Document Url=http://services.odata.org/northwind/northwind.svc/” _

, “””Order_Details””,””Orders”””, 6, True, False

End Sub

Creating from SQL server source instead

Creating from a Table

Sub AddSQLtables()

‘One or more tables from a SQL server database

‘Relationships are detected if they are declared in the database

ActiveWorkbook.Connections.Add2 _

“Connection Name”, “”, _

“OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=<SQL server – name>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AdventureWorksDW2008R2” _

, “””DimCustomer””,””DimGeography”””, 6, True, True

End Sub

Parameters need to be adjusted to your needs of course like server name, database and tables.

Notice the list of tables with the multiple quotes around them. You can include one or more tables or views. You can’t specify a subset of columns from a table.

The two True values tell Excel to add the tables to the model and to detect relationships.

Relationships between the table imported together are detected and if they exist they are added to the ModelRelationships collection

Creating from a SQL query

Sub Addsql()

‘A SQL query is used to create a table in the model.

‘ The table name cannot be changed and is <database name query>

ActiveWorkbook.Connections.Add2 _

“All fields from customer”, “”, _

“OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=<SQL server – name>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=AdventureWorksDW2008R2” _

, “select * from “”DimCustomer”””, 2, True, True

End Sub

I couldn’t find a way to determine the name of the new table in the model L

Creating from Access tables

Sub AccessTables()

‘A collection of tables from Access. Relationships are detected

ActiveWorkbook.Connections.Add2 “Sales Retail”, “”, Array( _

“OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password=””””;User ID=Admin;Data Source=C:\Users\danyh\Documents\Sales Retail.accdb;Mode=Share D” _

, _

“eny Write;Extended Properties=””””;Jet OLEDB:System database=””””;Jet OLEDB:Registry Path=””””;Jet OLEDB:Database Password=””””;Jet OLED” _

, _

“B:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet O” _

, _

“LEDB:New Database Password=””””;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don’t Copy Local” _

, _

“e on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet O” _

, _

“LEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False” _

), “””Employees””,””departments”””, 6, True, True

Probably many parameters are optional. Again the main thing are the two last parameters which add the tables to the model and trigger relationships detection.

From a CSV file

Sub FromCsv()

ActiveWorkbook.Connections.AddFromFile _

“C:\Users\danyh\Documents\MyTable.csv”, True, False

End Sub

This example is using a different method but the last two parameters are the same

From a table in the same file

Sub FromTable()

ActiveWorkbook.Connections.Add2 _

“WorksheetConnection_Import tables.xlsm!TableName”, “”, _

“WORKSHEET;C:\Users\danyh\Documents\Import tables.xlsm”, _

“Import tables.xlsm!TableName”, 7, True, False

End Sub

From ODATA feed

Sub TwotablesFromOData()

ActiveWorkbook.Connections.Add2 _

“DataFeed_2_services-odata-org Multiple Tables”, “”, _

“DATAFEED;Data Source=http://services.odata.org/northwind/northwind.svc/;Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=SSPI;Keep Alive=true;Persist Security Info=false;Service Document Url=http://services.odata.org/northwind/northwind.svc/” _

, “””Order_Details””,””Orders”””, 6, True, False

End Sub

  Subscribe to PowerPivotPro!
X

Subscribe

This Post Has 11 Comments

  1. What about changing the actual data source for a PowerPivot model?

    Suppose you have 3 environments (Development, Staging, Production) and you want to automate the deployment of many PowerPivot workbooks. Each PowerPivot model must be updated to change the SQL server it is pointed to e.g. (SQLDev, SQLStaging, SQLProd).

    Is it possible to change the PowerPivot data source?

  2. What is the VBA Syntax Using MS Query?

    I use an ODBC Connection (CONNX) to extract data from a legacy file structure.

    I have tried the following and get Run-time error 5: Invalid procedure call or argument

    The driver I am using can be accessed directly from PowerPivot … the connection string in that instance uses a syntax other than DSN.

    I am also guessing that the value to use in lCmdtype is SQL as that is what appears in the Connection Properties if I do a regular query from Excel.

    Would appreciate any feedback.

    Sub LoadTables()

    Dim ConnString As Variant
    Dim ComnandText As Variant

    ‘ ActiveWorkbook.Connections(“MurphyBI”).Delete

    ConnString = Array( _
    “DSN=MurphyBI;UID=murphy;PWD=murphy;”, _
    “NODE=192.168.36.1;APPLICATION=;” _
    )

    CommandText = Array( _
    “SELECT customer_code, name FROM customers” _
    )

    ActiveWorkbook.Connections.Add2 _
    Name:=”MurphyBI”, _
    Description:=”MurphyBI”, _
    ConnectionString:=Join(ConnString, “;”), _
    lCmdtype:=Sql, _
    CommandText:=Join(CommandText, “, “), _
    CreateModelConnection:=True, _
    ImportRelationships:=False

    End Sub

    1. In general you can’t add to the model a source that is not supported by the model.
      When you use MS-Query from the UI you don’t have the option of adding the data to model so you can’t do it from VBA either.
      If you would be successful I would consider it a bug 🙂
      I agree that the error message could be nicer than it is.

      1. Thank you for your quick reply.

        I suppose my confusion is coming from the fact that I can read data from my source in Excel using MSQuery & relevant ODBC Connector. From within PowerPivot I use the OLEDB/ODBC Connection directly (the connection syntax is slightly different) but can still access data from the same back-end. This works perfectly for data coming from RMS files on an Alpha Server using CONNX ODBC. It is because CONNX works in BOTH Excel & PowerPivot that I was attempting to use the VBA option, to check if I could get it to work.

        The reason for my interest is that there is another data source that I connect to using Informix ODBC. It seems to be an old Informix driver, yet it works adequately when accessed from Excel. However, it is totally unreliable (crashing!) when accessed from PowerPivot using the OLEDB/ODBC route. For example, while you may be able to load a table, it will crash when you attempt to Refresh – so it is not usable. I was hoping to exploit the fact that it works ok in Excel to get around the limitations in PowerPivot. PowerQuery is not an option (at this point in time) as it does not have support for OLEDB/ODBC connections. I am currently using a 2-step workaround … loading to an interim Excel Workbook and then from there to PowerPivot. The 1-Million rows of Excel is not likely to be an issue in this particular instance, but it is still cumbersome.

        Not sure if this changes anything?

  3. Hi,
    So there is definitely not a way to automatically replace calculated fields? My issue comes from when I have to unload the Model and then reload it. Obviously I lose customisations at this point, which I would love to be able to restore v swiftly..

    Any advice much appreciated..

    -CL

  4. This code for importing data from Excel file:

    Sub AddPowerPivotModel()

    ‘// workbook
    Dim wrkbk As Excel.Workbook
    Set wrkbk = Application.Workbooks.Add()

    ‘// full path of the excel filename
    Dim strPathWorkbook As String
    strPathWorkbook = “C:\MySheet.xlsx”

    ‘// name of sheet to import
    Dim strSheetName As String
    strSheetName = “Sheet1”

    ‘// construct connection to obtain data from strPathWorkbook
    Dim strConnection As String
    strConnection = “OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;” & _
    “Password=””””;User ID=Admin;” & _
    “Data Source=” & strPathWorkbook & “;” & _
    “Mode=Read;” & _
    “Extended Properties=””HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;””;” & _
    “Jet OLEDB:Engine Type=34;”

    ‘// import the excel sheet as Data Model
    Call wrkbk.Connection.Add2(“MyConnection”, “Product Model”, strConnection, “SELECT * FROM [” & strSheetName & “$]”, 2, True, True)

    End Sub

    1. Hi everybody,

      I have tried do this, but unfortunately does not works.

      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=SERVIDOR\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;”, _
      “””CATEGORIA””,””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.”

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

      [Dim xlApp As NEW Excel.Application ]

      But unfortunately , I need caught the current instance >> xlApp = Globals.ThisAddIn.Application

      ****You can see 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\xxx\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:

      Would you be able to help me ?

      Edson

Leave a Comment or Question