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 = “myDomainUser”

    ‘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