Skip to content


Tech Tuesday: Calling the SmartConnect REST Web Service from VBA in Excel

Today’s Tech Tuesday is going to be very focused on the topic of running a SmartConnect Map from VBA for Excel. In this article we will show you how to run a map in two ways, one by using the configured datasource and another by overwriting the datasource with data from the Excel workbook. These powerful web services for SmartConnect allow you to send Excel data to any system (ie. Dynamics CRM,, Dynamics GP, Web Services, etc)

We start out by opening the Developer pane of the Office Ribbon and selecting Visual Basic as the option. Now that we are inside of VBA, right-click on the Class Modules and select Insert-Class Module. At this point, you can copy/paste the piece of code at the bottom of this article in the new class you have created.VBA


There are few things going on in this code by let me focus specifically on the call to the SmartConnect REST Web Service outlined in the snippet below:


Start of Code to send data

    Set XMLHttpReq = New MSXML2.XMLHTTP60

    Set XMLHttpResult = New MSXML2.DOMDocument60


    With XMLHttpReq

         ‘.Open “POST”, “https://myWebServer:5557/smartconnect.svc/runmapxml/myMap”, False, webUser, webPass

        .Open “POST”, webService & “/” & webMethod & “/” & mapID, False, webUser, webPass

        .setRequestHeader “Content-Type”, “text/xml; charset=utf-8”

        .send myXML

    End With

End of Code to send data


The first two lines in this code snippet are setting up the Request to the Web Service and the Response back from the Web Service. In order to work with XML and web services in Excel we will need to add a reference to the Microsoft XML, 6.0 object like the diagram below, by going to Tools-References inside of VBA.



The second section of the code snippet is adding parameters to the Request. Now we will evaluate the three lines of the request:

  • .Open – This opens the call to the Web Service and passes in the URL, Asynch, User, and Password. In the URL we have specified to pass in a parameter from code above telling us which method to include in our path for the URL. The two methods we are focusing on today are RunMap and RunMapXML. The difference between the two is that one just calls a map to be run and the other actually sends in data to the Web Service to overwrite the default datasource. Below you can see a sample URL for each method:
    1. https://myWebServer:5557/smartconnect.svc/runmapxml/myMap
    2. https://myWebServer:5557/smartconnect.svc/runmap/myMap
    3. For more available methods, please reference our Online Manual: CLICK HERE
  • .setRequestHeader – This parameter just tells the request what type of document it is sending. In our case we will always use “Content-Type”, “text/xml; charset=utf-8” for this type of call.
  • .send – This parameter is what finally sends the request and in our example, actually sends out an XML document back to SmartConnect as our datasource. (I have included the code that calls a complex function to grab all values in a particular spreadsheet and include in an XML document in the code at the bottom of this article.

This example is based off code we use in our Excel Templates but it could be easily adapted for any programming language to call the SmartConnect REST Web Service. I hope I have inspired some of the coders out there to take advantage of some of the powerful features we have had in the product for many years.



Start Sample Excel VBA Function to send spreadsheet data to SmartConnect Web Service

Private Sub SmartConnectErrorHandler(str_Function As String)

    Err.Raise Err.Number, str_Function, Err.Description

End Sub


Public Function wsm_RunMapREST() As String

    Dim webService, webMethod, mapID, myXML, webUser, webPass As String


    ‘Set Connection info to SmartConnect Web Service

    webPass = “myPassword”

    webUser = “myDomain\User”

    ‘Get the Webservice from E5 on the SmartConnectConfig Sheet

    webService = Trim(Sheets(“SmartConnectConfig”).Cells(5, 5))

    webMethod = “runmapxml”

    ‘Get the Map from E3 on the SmartConnectConfig Sheet

    mapID = Trim(Sheets(“SmartConnectConfig”).Cells(3, 5))


    ‘Error Trap

    On Error GoTo wsm_RunMapREST


    ‘Set objXMLdoc = CreateObject(“Microsoft.XMLDOM”)

    Set objXMLdoc = GenerateXMLDOM(DataRange(), “DataSet”)

    myXML = objXMLdoc.DocumentElement.XML


    Set XMLHttpReq = New MSXML2.XMLHTTP60

    Set XMLHttpResult = New MSXML2.DOMDocument60


    With XMLHttpReq

        .Open “POST”, webService & “/” & webMethod & “/” & mapID, False, webUser, webPass

        .setRequestHeader “Content-Type”, “text/xml; charset=utf-8”

        .send myXML

    End With


    wsm_RunMapREST = XMLHttpReq.responseText


Exit Function


    SmartConnectErrorHandler “wsm_RunMapREST”

End Function

End Sample Excel VBA Function to send spreadsheet data to SmartConnect Web Service



  1. Thomas Moore on January 24, 2017 at 8:22 am

    How about the use of a parameter and the /var option?

  2. Jesus Otero on February 6, 2018 at 6:35 pm

    Is it possible to return additional information from the map? Let’s say a GP Rolling column if the Map succeeds?

Leave a Comment


Tech Tuesday: Popdock - Joining the tale of two States
Meet eOne Solutions at Directions EMEA 2023!
Connect with eOne Solutions at SuiteWorld 2023!
What is a Data Lake/Data Lake Management?
The Challenges & Benefits of Ad Hoc Reporting


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder



Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release Salesforce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.