“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.
1. Operation of Users
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.
2. Overview of collaboration between Excel and Questetra
The outline of the flow is as follows.
- When the button on the Excel spreadsheet is cicked, the macro (VBA) is executed.
- Extract data in each cell of the spreadsheet by macro (VBA), and send them to Questetra in HTTP request format.
- Questetra receives the request with “Message Start Event (HTTP)” then Start a Workflow.
3. Prerequisite knowledge
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.
4. Details of implementation
4-1. Preparations on Questetera
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.
4-2. Preparation on Excel
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.input" values(i) = Range("C4").Value i = i + 1 params(i) = "data.datetime" values(i) = Range("C5").Value i = i + 1 params(i) = "data.datetime" values(i) = Range("C6").Value i = i + 1 params(i) = "data.input" values(i) = Range("D7").Value i = i + 1 params(i) = "data.input" values(i) = Range("D8").Value i = i + 1 params(i) = "data.input" values(i) = Range("D9").Value i = i + 1 params(i) = "data.input" values(i) = Range("C10").Value i = i + 1 params(i) = "data.input" values(i) = Range("C11").Value i = i + 1 params(i) = "data.input" values(i) = Range("C12").Value i = i + 1 params(i) = "data.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.
- 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.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.
Apply for Starter Plan (Free) Here
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.