How to write to multiple columns in ActiveData with a .NET script

How to write to multiple columns in ActiveData with a .NET script

In this article, we will demonstrate how to write to multiple columns within an ActiveData asset.

The below example uses a sample SAP Application where we will be retrieving the data from multiple columns within a SapTable control and writing to separate columns in the ActiveData asset where the ActiveData asset is a .xlsx file.

To begin, we retrieve the data from the table control with each columns data added to separate lists; 'accounts' and 'descriptions'.

The script will then loop through every item in each list using a For Each statement. This For Each statement will call our custom function 'AddToBook'.

The 'AddToBook' function accepts three parameters; 'item', 'columnId' and 'cRow'. The 'item' is a string representation of each individual data item in the respective list. The 'columnId' is the name (header) of the column in the Excel sheet which you wish to write the data too and 'cRow' is the current row index which the data will be written to.

The function declares an ActiveDataRow which is assigned to a specified row index using the 'item(cRow)' method. 

Dim row As ActiveDataRow = data.item(cRow)

 Next you will call the 'SetString' method for each ActiveDataRow and pass in the 'columnId' and 'item' values.

row.SetString(columnId, item)

To finish, the function then saves the currently loaded ActiveData.

During the For Each statement, you need to increment the existing row index which you wish to write to. We have initially declared an Integer called 'currentRow' with a value of 1. This is the row to start writing to. We cannot use '0' as this would be the header of the column.

Once AddToBook has been called, you simply increment the currentRow value by calling 'currentRow +=1'. Therefore on the next loop, the row index will be 2 and then 3 and so forth.

After the first For Each statement, it is important to reset the currentRow value to 1 so that the next column will not start at the end of the last columns length.

 

Public Module Main
    Dim _desktop As Desktop = Agent.Desktop
    Dim data As ActiveData = Workbench.LoadActiveData("SapData")
    
    Public Sub Main()
        
        With _desktop.SapWindow("@automationId='/app/con[0]/ses[0]/wnd[0]'")
            .SetActive()

            'populate lists from application table columns
            Dim accounts As List(Of SapTextField) = .FindAll(Of SapTextField)("//SapTextField[@Left='80']")
            Dim descriptions As List(Of SapTextField) = .FindAll(Of SapTextField)("//SapTextField[@Left='183']")
            
            Dim currentRow As Integer = 1
                                                                    
                For Each acc In accounts
                    AddToBook(acc.Text, "Account", currentRow)
                    currentRow +=1
                Next
                
                'reset row index
                currentRow = 1
                                
                For Each desc In descriptions        
                    AddToBook(desc.Text, "Description", currentRow)
                    currentRow += 1
                Next
                
                data.Save()    
                data.Dispose()
                                
        End With
            
    End Sub
    
    Public Function AddToBook(item As String, columnID As String, cRow As Integer)
                
        Dim row As ActiveDataRow = data.Item(cRow)        
        row.SetString(columnID, item)                
        data.Save()    
        
    End Function
        
End Module

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2018-06-07 12:20
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.