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 :)

6 Responses to Import data into the model using a Macro in Excel 2013

  1.  

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

  2.  

    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!

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

    Thanks,
    Kasper

     
  4. 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.

     
  5.  

    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

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

       

leave your comment


five − 4 =