Adding tables to a Power Pivot model from VBA (in Excel 2013)

August 25, 2014 at 3:05 pm

Thanks for the information. How can I add data from an XML file stored in our internal SharePoint?

September 18, 2014 at 4:45 am

You can try using Power Query and then either send the data to an Excel sheet or directly to Power Pivot (Data Model). See:-
Download Microsoft Power Query for Excel
Introduction to Microsoft Power Query for Excel
Power Query connect to an XML file

October 1, 2014 at 1:45 pm

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?

July 22, 2017 at 2:58 pm

This is entirely my problem — I have around 50 models to update after switching to a new production server. (If I can work it out I’ll pop back with the answer.)

October 24, 2014 at 5:04 am

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

October 26, 2014 at 8:28 am

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.

October 27, 2014 at 5:09 am

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?

May 18, 2015 at 4:33 am

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

September 14, 2015 at 7:39 am

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

November 23, 2015 at 10:49 pm

How about connection example to oracle? Because i try and still doesn’t work. Need your help. Thx…..

February 4, 2017 at 10:05 pm

How about insert a relationship between 2 tables with date hierarchie? f

May 9, 2017 at 3:37 pm

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

December 12, 2017 at 4:48 pm

Hello,

I have one table “Table1” and on another worksheet I have over a dozen pivot tables that point to Table1, however at the time of creation I was unaware of the check box “Add data to the data model”, so now I’m unable to use slicers to adjust all my pivot tables. Is there a way to add existing pivot tables to the data model?

Thanks,
Chris

February 16, 2018 at 10:09 am

Im trying to write a macro that looks for a sheet + table, if it exists, it updates the existing table value, if it doesnt; it creates a new sheet, adds a new table to it. This first part is simple using some If constructions.

However, I am trying to also make macro add this new table to the data model for PowerPivot, but I am having trouble making the connection string work with an object name that is a string linked to a variable instead of a hard programmed table name. Could I use

for example:

Dim shName As String

shName = range(“A1”)

Workbooks(“Model.xlsm”).Connections.Add2 _
“WorksheetConnection_Model.xlsm!shName”, “”, _
“WORKSHEET;../../Model.xlsm”, _
“Model.xlsm!shName”, 7, True, False

Any thoughts? Is this possible?

Jackson

February 16, 2018 at 10:14 am

Premature post. Solved below

Workbooks(“Model.xlsm”).Connections.Add2 _
“WorksheetConnection_Model.xlsm!” & shName, “”, _
“WORKSHEET;../../Model.xlsm”, _
“Model.xlsm!” & shName, 7, True, False

August 1, 2018 at 4:24 am

I am using VBA to create Datamodel and create relationship of data from same excel file, unfortunately i am not able to achieve adding calculated columns in powerpivot table. Any thoughts on this?

here is my code sample….
ActiveWorkbook.Connections.Add2 _
“WorksheetConnection_Book1.xlsm!Table1”, “”, _
“WORKSHEET;C:\Book1.xlsm”, _
“Book1.xlsm!Table1”, 7, True, False

ActiveWorkbook.Connections.Add2 _
“WorksheetConnection_Book1.xlsm!Table2”, “”, _
“WORKSHEET;C:\Book1.xlsm”, _
“Book1.xlsm!Table2”, 7, True, False

Dim Modl As Model
Set Mod1 = ActiveWorkbook.Model

‘active relationship
Mod1.ModelRelationships.Add Mod1.ModelTables(“Table1”).ModelTableColumns(“Worker ID”), Mod1.ModelTables(“Table2”).ModelTableColumns(“Supervisor ID”)

August 31, 2018 at 9:15 am

Thank you…Great article!
But I haven’t found the case with importing data from MySQL. I tryed some modification with your “AddSQLtables” example…but it doesn’t work. Could you provide with MySQL example?

Thanks

May 14, 2019 at 9:34 am

Hello, baldmosher.
You you find a way to how change actual data source for a PowerPivot model (ConnectionString)?

Adding tables to a Power Pivot model from VBA (in Excel 2013)

Guest Post by Dany Hoter

Creating from SQL server source instead

Creating from a Table

Creating from a SQL query

Creating from Access tables

From a CSV file

From a table in the same file

From ODATA feed

Cancel reply