Import data into the model using a Macro in Excel 2013

In Excel 2013 Macro’s now can talk to the Embedded Analysis Services engine. Here is an example created by my colleague Allan Folting that will import a table from the Azure datamarket into the Model using the Excel OM.

The first thing you want to do when you want to create macro’s is enable the Developer ribbon of Excel.

Right mouseclick on the ribbon, and click Customize the Ribbon:

This opens the Excel options dialog, select the developer Tab:

This will open the Developer bar:

 

Now click in Visual Basic and off you go :).

The procedure Allan created below can be pasted into the Macro.  This code will add a connection to the model that points to the Windows Azure Datamarket . Notice the last parameter on the Connection.Add2 that says  “CreateModelConnection:=True” , that will make sure the connection is created on the embedded model. Also make sure you get your own azure data market key by signing up to datamarket. You can remove the password part of the connectionstring below, that will prompt a dialog for the user running the macro to give the accountkey needed to import the data.

After the connection is created the data from the data source is used to populate a new table on the worksheet. The “.Refresh” will make sure that the data gets transferred from the source to the Data Model.

Sub ImportDataFromFeedToTable()
Dim MyConnStr As String

MyConnStr = "DATAFEED;Data Source=https://api.datamarket.azure.com/Data.ashx/Esri/KeyUSDemographicsTrial/ " & _
" v1/demog1?$top=100;Namespaces to Include=*;Max Received Message Size=4398046511104; " & _
" Integrated Security=Basic;User ID=AccountKey;Persist Security Info=false; " & _
" Base Url=https://api.datamarket.azure.com/Data.ashx/Esri/KeyUSDemographicsTrial/v1/demog1?$top=100"

ActiveWorkbook.Connections.Add2 Name:="AzureDataMarketPlaceDataFeed", _
                                       Description:="My Data Feed", _
                                       ConnectionString:=MyConnStr, _
                                       CommandText:="demog1", _
                                       CreateModelConnection:=True
  With ActiveSheet.ListObjects.Add(SourceType:=4, _
                         Source:=ActiveWorkbook.Connections("AzureDataMarketPlaceDataFeed"), _
                         Destination:=Range("$A$1")).TableObject
      .ListObject.DisplayName = "Table_demog1"
      .Refresh
  End With
End Sub

Running this macro will add the data to a worksheet:

And makes it available in the model:

of course this is just a small example, many more things are possible using excel macro’s.

 

Happy coding :)

  • http://thedataspecialist.wordpress.com/ Laurent Couartou

    Can you confirm there is no option to create calculated fields through VBA? If yes, do you know the reason why?

  • greg kramer

    granted i have not spent much time debugging, but on connections.add2 it’s throwing the error–>object doesn’t support this property or method

    suggestions?

    thanks!

  • Kasper de Jonge

    Hi @greg kramer ,
    Did you use my exact example ?

    Thanks,
    Kasper

  • Kasper de Jonge

    Hi @Laurent Couartou
    Yes, the reason is probably that this feature didn’t make it into the release. This is owned by Excel so I don’t know all the details.

  • Ajay Kant Singh

    Hi Kasper,

    How can we add connection to workbook without adding data to data model? I tried setting createModelConnection as false, the program says invalid parameter passed.

    Thanks,
    Ajay

    • Kasper de Jonge

      I dont know excatly, I would try and start macro recording and then do the action you want to see what the code is.