In this article, Jibril Khedimallah proposes a basic approach of Convertigo C-EMS using Microsoft Excel.

{rdaddphp file=forms/select_codePre.php}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44  Private Sub BrowseXMLDocument(ByVal vXML As String)
    Dim xmlDoc As domDocument, root As IXMLDOMElement
    Dim i As Long
    Dim c As Long
 
    Set xmlDoc = New domDocument
    xmlDoc.async = False
    xmlDoc.LoadXML (vXML)
 
    Set root = xmlDoc.DocumentElement
 
    If Not root Is Nothing Then
        If wks.UsedRange.Cells.Count = 1 Then
            Set rng = wks.Cells(1)
        Else
            Set rng = wks.Cells(wks.UsedRange.Rows.Count + 1, 1)
        End If
        With rng
            .Value = root.BaseName
            .Offset(0, 1).Value = root.nodeTypeString
            .Offset(0, 2).Value = root.NodeValue
            .Offset(0, 3).Value = root.Text
            For c = 0 To root.Attributes.Length - 1
             .Offset(0, c + 4).Value = root.Attributes.Item(c).BaseName
             .Offset(0, c + 5).Value = root.Attributes.Item(c).NodeValue
            Next c
        End With
        BrowseChildNodes root
    End If
    wks.Cells(1).EntireRow.Insert xlShiftDown
    With wks.Cells(1)
        .Value = "baseName"
        .Offset(0, 1).Value = "nodeTypeString"
        .Offset(0, 2).Value = "nodeValue"
        .Offset(0, 3).Value = "text"
        c = 1
        For i = 4 To wks.UsedRange.Columns.Count - 1 Step 2
            .Offset(0, i).Value = "attribute" & c
            .Offset(0, i + 1).Value = "Value" & c
            c = c + 1
        Next i
    End With
    wks.Rows(1).Font.Bold = True
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 Private Sub BrowseChildNodes(root_node As IXMLDOMNode)
    Dim i As Long
    Dim c As Long
    Dim rng As Range
 
    For i = 0 To root_node.ChildNodes.Length - 1
        If root_node.ChildNodes.Item(i).NodeType  3 Then
            If wks.UsedRange.Cells.Count = 1 Then
                Set rng = wks.Cells(1)
            Else
                Set rng = wks.Cells(wks.UsedRange.Rows.Count + 1, 1)
            End If
            With rng
                .Value = root_node.ChildNodes.Item(i).BaseName
                .Offset(0, 1).Value = root_node.ChildNodes.Item(i).nodeTypeString
                .Offset(0, 2).Value = root_node.ChildNodes.Item(i).NodeValue
                .Offset(0, 3).Value = root_node.ChildNodes.Item(i).Text
                For c = 0 To root_node.ChildNodes.Item(i).Attributes.Length - 1
                   .Offset(0, c + 4).Value = root_node.ChildNodes.Item(i).Attributes.Item(c).BaseName
                   .Offset(0, c + 5).Value = root_node.ChildNodes.Item(i).Attributes.Item(c).NodeValue
               Next c
            End With
        End If
        BrowseChildNodes root_node.ChildNodes(i)
    Next
End Sub

Thanks to Convertigo’s C-EMS solution it’s easy to cost-effectively track down the data that you need, from wherever it is. And sometimes, these data required to be analyzed and interpreted. In order to fulfill these requirements, Microsoft Excel represents the most popular and efficient solution.

That’s why we are proposing you a Microsoft Excel workbook solution with which it is possible to call Convertigo sequences or Convertigo transactions, and to put the returned results into a Microsoft Excel sheet format, allowing to you the entire ownership of the extracted data.

Download the solution: Convertigo_excel.zip

Using Convertigo C-EMS through Microsoft Excel

In order to execute Convertigo sequences or Convertigo transactions from Microsoft Excel, a basic interface is available in the workbook. This interface is as a form where it’s possible to fill several fields in order to execute the wanted Convertigo C-EMS action. This workbook has been tested on Microsoft Excel 2003 and Microsoft Excel 2007 versions.

The form

Parameters:

Convertigo Server: Indicates the targeted Convertigo server, formatted as “address/name”.

Project Name: Here you can enter the name of the Convertigo project to use.

Connector: Here you can enter the name of the connector containing the sequence or transaction to be called. You can also leave it blank to call the default one.

Sequence: Here you can enter the name of the Convertigo sequence to be called.

Transaction: Here you can enter the name of the Convertigo transaction to be called.

Sequence and Transaction can’t be called both at the same time, so either a sequence or a transaction can be entered.

Parameter: This is possible to add as many parameters as needed for the Convertigo sequence or the Convertigo transaction to be called. After entering the name and the value of the parameter, you can add it. You can also delete a parameter by selecting it and clicking the “Delete” button.

Executing a request may take several seconds, according to the size of returned data, you must be patient :). Once the request is done, a new Microsoft Excel worksheet appears containing the execution results. A new worksheet will be created for each execution.

Pre-requisites:

Performing Visual Basic application code (see “Solution’s architecture” paragraph), the workbook needs macros to be enabled. If it’s not, please follow the instructions below:

Microsoft Excel 2007:

Click the Microsoft Office Button , and then click Excel Options

Click Trust Center, click Trust Center Settings, and then click Macro Settings

Click the options Trust access to the VBA project object mode

Microsoft Excel 2003:

On the Tools menu, point to Macro, and then click Security

On the Trusted Publishers tab, select the Trust all installed add-ins and templates check box

Don’t forget to restart the workbook after having applied the settings.

Use case

To a better understanding, let’s perform the demonstration “demo_usDirectory” available on the demos’ server of Convertigo “demo.convertigo.net/cems”.

We will use an existing project, so you should be able to easily reproduce this sample.

The purpose of this use case will be to call the transaction usdSearchByName available in the Convertigo’s demonstration project demo_usDirectory. Through this transaction, Convertigo C-EMS will perform a search on the http://www.usdirectory.com/ web site and return the resulting data as an XML document.

So let’s fill the request form in order to search the Adobe Company located in San Francisco, California:

Filled Form

Once the form has been filled, you can execute the given sequence or transaction by clicking the Execute button. This action can take several seconds and will open a new Microsoft Excel worksheet containing the generated XML mapped into a Microsoft Excel worksheet format:

Execution results

The data now available in a Microsoft Excel worksheet, you can manipulate them as you want.

Solution’s architecture

This solution is based on the VBA (Visual Basic Application) programming language. Interpreted by Microsoft Excel, the VBA language allows us to automate processes and increase our efficiency.

So, through a VBA script integrated in the Microsoft Excel workbook, it is possible to execute a Convertigo sequence or a Convertigo transaction and get the result just by clicking the button Execute:

Visual Basic Application Solution

Because the Convertigo Server returns the transaction or sequence response as an XML document, mapping this one is needed. This mapping determinates how the data will be organized in the Microsoft Excel “ExecutionResults” worksheet.

That’s why, if you want to modify the way the VBA script is rendering the result, you must modify the following methods in order to fulfill your expectations (the folowing methods will display the entirety of the XML document):

BrowseXMLDocument:

Code vba :

Select all – Show all

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44  Private Sub BrowseXMLDocument(ByVal vXML As String)
    Dim xmlDoc As domDocument, root As IXMLDOMElement
    Dim i As Long
    Dim c As Long
 
    Set xmlDoc = New domDocument
    xmlDoc.async = False
    xmlDoc.LoadXML (vXML)
 
    Set root = xmlDoc.DocumentElement
 
    If Not root Is Nothing Then
        If wks.UsedRange.Cells.Count = 1 Then
            Set rng = wks.Cells(1)
        Else
            Set rng = wks.Cells(wks.UsedRange.Rows.Count + 1, 1)
        End If
        With rng
            .Value = root.BaseName
            .Offset(0, 1).Value = root.nodeTypeString
            .Offset(0, 2).Value = root.NodeValue
            .Offset(0, 3).Value = root.Text
            For c = 0 To root.Attributes.Length - 1
             .Offset(0, c + 4).Value = root.Attributes.Item(c).BaseName
             .Offset(0, c + 5).Value = root.Attributes.Item(c).NodeValue
            Next c
        End With
        BrowseChildNodes root
    End If
    wks.Cells(1).EntireRow.Insert xlShiftDown
    With wks.Cells(1)
        .Value = "baseName"
        .Offset(0, 1).Value = "nodeTypeString"
        .Offset(0, 2).Value = "nodeValue"
        .Offset(0, 3).Value = "text"
        c = 1
        For i = 4 To wks.UsedRange.Columns.Count - 1 Step 2
            .Offset(0, i).Value = "attribute" & c
            .Offset(0, i + 1).Value = "Value" & c
            c = c + 1
        Next i
    End With
    wks.Rows(1).Font.Bold = True
End Sub

BrowseChildNodes:

Code vba :

Select all – Show all

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 Private Sub BrowseChildNodes(root_node As IXMLDOMNode)
    Dim i As Long
    Dim c As Long
    Dim rng As Range
 
    For i = 0 To root_node.ChildNodes.Length - 1
        If root_node.ChildNodes.Item(i).NodeType  3 Then
            If wks.UsedRange.Cells.Count = 1 Then
                Set rng = wks.Cells(1)
            Else
                Set rng = wks.Cells(wks.UsedRange.Rows.Count + 1, 1)
            End If
            With rng
                .Value = root_node.ChildNodes.Item(i).BaseName
                .Offset(0, 1).Value = root_node.ChildNodes.Item(i).nodeTypeString
                .Offset(0, 2).Value = root_node.ChildNodes.Item(i).NodeValue
                .Offset(0, 3).Value = root_node.ChildNodes.Item(i).Text
                For c = 0 To root_node.ChildNodes.Item(i).Attributes.Length - 1
                   .Offset(0, c + 4).Value = root_node.ChildNodes.Item(i).Attributes.Item(c).BaseName
                   .Offset(0, c + 5).Value = root_node.ChildNodes.Item(i).Attributes.Item(c).NodeValue
               Next c
            End With
        End If
        BrowseChildNodes root_node.ChildNodes(i)
    Next
End Sub

By Jibril Khedimallah | 8 July 2011 | In Uncategorized | Leave a comment |

Interfacing Convertigo with Microsoft Excel

In this article, Jibril Khedimallah proposes a basic approach of Convertigo C-EMS using Microsoft Excel.

Using Convertigo C-EMS through Microsoft Excel

The form

Parameters:

Pre-requisites:

Don’t forget to restart the workbook after having applied the settings.

Use case

Filled Form

Execution results

Solution’s architecture

Visual Basic Application Solution

Leave a comment Cancel comment