EONE BLOG

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, Salesforce.com, 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.

References

 

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.

Thanks,
Chris

 

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

wsm_RunMapREST:

    SmartConnectErrorHandler “wsm_RunMapREST”

End Function

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

 

2 Comments

  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





RECENT POSTS


The Pick Popdock Papers, Part IV: That's nice, but what else can it do?
Tech Tuesday: Setting up the NetSuite Connector in SmartConnect.com
Tech Tuesday: Integrating Record Notes in Dynamics GP with SmartConnect
Targeting Your Audience Based On Contacts From Multiple Applications With Popdock
eOne Offices Closed for Labor Day, Monday, September 2nd

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.