How to Easily Start a Workflow from an Excel Spreadsheet

Hi, there.

“Want to realize a Workflow system using Excel spreadsheet which already has implemented.” That is what I often hear as a desire of business people.

The reasons why is the following.

• Reduction of labor for instruction for users
• If it is Excel, even users who are less computer literate can handle

Therefore, I will introduce a method for Starting a Workflow on Questetra BPM Suite triggered by an entering to Excel spreadsheet by a User, in this post.

When a user enters data into a spreadsheet as conventionally then click on “Submit” button which we are going to arrange, a corresponding Workflow of Questetra is Started and it will flow on.

The outline of the flow is as follows.

1. When the button on the Excel spreadsheet is cicked, the macro (VBA) is executed.
2. Extract data in each cell of the spreadsheet by macro (VBA), and send them to Questetra in HTTP request format.
3. Questetra receives the request with “Message Start Event (HTTP)” then Start a Workflow.

Although knowledge of the Excel macro (VBA) is required, you can understand immediately if you are experienced in programming.
Even if you do not have programming experience, it is a simple level content that you can understand sufficiently if you study a little.

Prepare a Workflow App which will Start when data is sent from Excel.
A receiver for data, which is referred to “Message Start Event (HTTP)”, is needed.
* Related manual M221: Auto Starting Triggered by HTTP Request

* Note that the connection part with Excel will not be fixed unless you release the App. After release, when you open “URL ・ Parameter Details” in the property of “Message Start Event (HTTP)”, the following screen will be displayed, so in the next, prepare Excel side in accordance with the content.

Prepare Excel VBP in accordance with the format of the spreadsheet. (Being based on the following sample code.)

* You can download a sample Excel spreadsheet file including sample codes, and a Workflow App archive (Admittance Application) HERE. Even when you try the sample spreadsheet as it is, you need to modify the code at least in line #12 to #15 to match the Questetra BPM suite environment to which you imported the APP.

* Regarding how to set up Excel VBA (macro), I omit the details of it since it is a knowledge in general. Please refer to web sites like THIS.

Sample codes (Request for Admittance)

Sub Button1_Click()

    Dim params() As String
    Dim values() As String

    ReDim params(16)
    ReDim values(16)

    Dim i As Integer
    i = 0

    Dim url As String
    Dim processModelInfoId As String
    Dim nodeNumber As String
    Dim key As String
    url = "https://xxxxxxxx/System/Event/MessageStart/start"
    processModelInfoId = "999"
    nodeNumber = "0"
    key = "xxxxxxxx"

    params(i) = "data[0].input"
    values(i) = Range("C4").Value
    i = i + 1
    params(i) = "data[1].datetime"
    values(i) = Range("C5").Value
    i = i + 1
    params(i) = "data[2].datetime"
    values(i) = Range("C6").Value
    i = i + 1
    params(i) = "data[3].input"
    values(i) = Range("D7").Value
    i = i + 1
    params(i) = "data[4].input"
    values(i) = Range("D8").Value
    i = i + 1
    params(i) = "data[5].input"
    values(i) = Range("D9").Value
    i = i + 1
    params(i) = "data[6].input"
    values(i) = Range("C10").Value
    i = i + 1
    params(i) = "data[7].input"
    values(i) = Range("C11").Value
    i = i + 1
    params(i) = "data[8].input"
    values(i) = Range("C12").Value
    i = i + 1
    params(i) = "data[9].input"
    values(i) = Range("C13").Value
    i = i + 1

    send url & "?processModelInfoId=" & processModelInfoId & "&nodeNumber=" & nodeNumber & "&key=" & key, params, values
End Sub

Public Function send(url As String, params() As String, values() As String) As String
    Dim httpObj As Object
    Set httpObj = CreateObject("MSXML2.XMLHTTP")

    Dim parameters As String
    parameters = ""
    For i = 0 To UBound(params)
        If Len(parameters) > 0 Then
            parameters = parameters & "&"
        End If
        parameters = parameters & params(i) & "=" & values(i)
    Next

    httpObj.Open "POST", url, False
    httpObj.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded" & vbCrLf
    httpObj.send (parameters)

    If httpObj.Status = 200 Then
        MsgBox ("Submt successfully")
    Else
        MsgBox ("Submit failure" & vbCrLf & "status=" & httpObj.Status)
    End If
End Function

The parts you need to modify are as follows.

• • The title of sub in line #1, modify it to match the button you added.

Sub Button1_Click()

• • When the number of data to be sent from the spreadsheet is more, modify the number in parameter and value in line #4 to #5

    ReDim params(16)
    ReDim values(16)

• • url, processModelInfoId, nodeNumber, key, in line #12 to #15, modify these to match the details of “Message Start Event (HTTP)” of Questetra.

    url = "https://xxxxxxxx/System/Event/MessageStart/start"
    processModelInfoId = "999"
    nodeNumber = "0"
    key = "xxxxxxxx"

• • Line #16 and after, modify these so that location of the cells in the spreadsheet to extract data matches the receiving parameter name in “Message Start Event (HTTP)”. In case if the number of data is more, add the following 3 lines by copy & paste. (params is for the sending parameter name, and Range is for the location of the cell)

    params(i) = "data[0].input"
    values(i) = Range("C4").Value
    i = i + 1

* Nothing is needed to be modified in Function in the last half, send()

A Process is supposed to be Started on Questetra when you click on the “Submit” button and a message, “Submit successfully” is displayed. If the message was “Submit failure”, the following causes can be considered.

* In cases if the error occurred on the Questetra side, an error notification is delivered to the App Administrator. Please check.

• The settings in between url and key do not match the setting of Questetra BPM Suite side
• The setting of each parameter name to send data does not match the setting on Questetra BPM Suite side
• Data that has been set to “required” on the Questetra BPM Suite side has not been sent
• Data type such as Select type, User type, Date type, DateTime type does not match the data format for Questetra BPM Suite

Even though some knowledge of VBA is needed, I suppose now you understand that application forms in excel spreadsheet can be used as an input form of Questetra with relatively simple settings.

If you have any questions, please feel free to Contact us.

By applying for your Free account of your own Questetra via the Web form HERE, you will be able to use all the Questetra features including what I mentioned above.