I find DDT is short supplied for methods. Like to browse across the cells I have to keep looping around with the "Next" function because cannot access the cells directly and for a "Next" I don't find a "Previous" that can help me browse backward. Also I could not find any method that could help me write data into the spreadsheet. Say I want to use an order number generated in a flow as input in the flow later and therefore want to store in my data store. I found COM as the coolest approach. Wrote a custom script extn with an object called DataSheet.
In the script I have would use
Result = DataSheet.GetAccess(FileName)
Result = DataSheet.LooseAccess(FileName)
Result = DataSheet.GetData(Sheetname, FieldName, Iteration)
Result = DataSheet.SetData(Sheetname, FieldName, Iteration, Value)
Result = DataSheet.FindValue(Sheetname, Value, SearchAllBool)
Result = DataSheet.SortValues(SheetName, FieldName, AscDesBool)
Result = DataSheet.FieldResultsCompare(Sheet1, Field1, Sheet2, Field2, ResSheet, ResField)
etc
Implementations would be like
Dim objExcel
Dim objWorkbook
Function DataSheet_GetAccess(WorkBookName)
Dim FilePath
FilePath = ProjectSuite.Path & "TestData\" & WorkBookName
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists (FilePath) then
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(FilePath)
DataSheet_GetAccess = 1
Else
DataSheet_GetAccess = 0
End if
End Function
Function DataSheet_GetData(WorkSheetName, IterationNumber, FieldName)
Set objWorksheet = objWorkbook.Worksheets(WorkSheetName)
i = 1
Do While objWorksheet.Cells(1, i).Value <> ""
If objWorksheet.Cells(1, i).Value = FieldName Then
DataSheet_GetData = objWorksheet.Cells(IterationNumber, i).Value
Exit Do
End If
i = i + 1
Loop
Set objWorksheet = Nothing
End Function
...
...