How to Integrate IBM Watson with Microsoft Excel

Integrate IBM Watson with Microsoft Excel

• Begin by ensuring that you have an IBM Cloud account and have set up the requisite IBM Watson services (e.g., Watson Language Translator, Watson Assistant, etc.). This step is crucial as it forms the basis for accessing IBM Watson APIs.

 

• Proceed to create API keys and service URLs from your IBM Cloud dashboard. Store these in a secure location as they will be necessary for the API integration with Excel.

# This will be your example API key
apikey = "your-ibm-watson-api-key"
# This will be your example service URL
service_url = "your-ibm-watson-service-url"

Set Up Microsoft Excel

• Open Microsoft Excel. Ensure that you have the Developer tab active. To activate, go to "File" > "Options" > "Customize Ribbon" and check the Developer option.

 

• Navigate to the Developer tab and select "Visual Basic" to open the VBA Editor. This development environment is essential for writing scripts that enable communication between Excel and IBM Watson services.

Write a VBA Macro for API Integration

• In the VBA editor, insert a new module by right-clicking on any of the existing objects in the Project Explorer and selecting "Insert" > "Module". This new module will host your macro.

 

• Implement a macro to make API calls to IBM Watson. You will use VBA’s built-in `XMLHttpRequest` object. Below is a sample code snippet for making API requests:

Sub CallIBMService()

    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim requestBody As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    apiKey = "your-ibm-watson-api-key"
    url = "your-ibm-watson-service-url"
    
    ' Prepare your request body according to the service requirements
    requestBody = "{ ""text"": ""Hello, world!"" }"
    
    ' Construct the HTTP request
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Basic " & Base64Encode("apikey:" & apiKey)
    
    ' Send the request
    http.send requestBody
    
    ' Handle the response
    If http.Status = 200 Then
        Debug.Print http.responseText
    Else
        Debug.Print "Error: " & http.Status & " | " & http.responseText
    End If

End Sub

Function Base64Encode(text As String) As String
    Dim arrData() As Byte
    arrData = StrConv(text, vbFromUnicode)
    Base64Encode = EncodeBase64(arrData)
End Function

Function EncodeBase64(arrData() As Byte) As String
    Dim objXML As Object
    Dim objNode As Object
    
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing
End Function

Execute the Macro in Excel

• Return to Excel, and run your VBA macro by pressing "Alt + F8", selecting "CallIBMService", and clicking "Run". Make sure your active sheet can display the results or handle errors returned from the IBM Watson service.

 

• Monitor for any errors or logs as indicated in the Debug.Print output. Adjust parameters and request headers as needed based on specific IBM Watson service requirements and your business use case.