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 🙂