Forum Discussion

S_Leonardo's avatar
S_Leonardo
Occasional Contributor
8 years ago
Solved

DDT object not reading excel values properly

Hi all,

 

I have several scripts that read data from excel sheets using DDT objects and i'm having problem with one of them.

 

I can't explain the problem using just words so i made a little script (vbscript) to simulate the problem, i will be immensaly grateful if somebody can run this for me and try to understand.

 

It's just 2 subs that do the exact same thing using different objects (DDT and WorkBook), read an excel sheet and log all values, it's supposed to generate the same result on both Subs.

I'm also attaching 2 .XLS files, they are very similar, one works with DDT, but ther other don't.

 

usingWorkBook - This one works, it read all values from all files properly

usingDDT - This one read properly 'Sheet_B', but with 'Sheet_A' it read until some point, then just read 'null' (all last 4 values).

TestComplete Version - 11.31.2420.7

 

You will need to edit the first line of each sub (variable 'FilePath') before running them.

You just need to run the Sub usingDDT to simulate the problem, i created the other sub just to make sure it works and the problem was not in my .XLS file.

 

 

 

Sub usingDDT
  FilePath = "C:\Users\leonardo\Desktop\Sheet_A.xls"  
  '
  Set objDDT = DDT
  Call objDDT.ExcelDriver(FilePath, "Plan1") 
  line = 0
  While Not objDDT.CurrentDriver.EOF
    line = line + 1
    result = ""
    For Col = 0 To objDDT.CurrentDriver.ColumnCount - 1
      Value = objDDT.CurrentDriver.Value(Col)
      ColName = objDDT.CurrentDriver.ColumnName(Col)
      If IsNull(Value) Then Value = "null"
      '
      result = result & ColName & ": " & Value & " "
    Next
    Log.Message("Row : " & line & " " & result)
    objDDT.CurrentDriver.Next
  Wend
  objDDT.CloseDriver(objDDT.CurrentDriver.Name)  
End Sub

Sub usingWorkBook
  FilePath = "C:\Users\leonardo\Desktop\Sheet_A.xls"
  '
  Set XLS = Createobject("Excel.Application")  
  Set WorkBook =  XLS.WorkBooks.Open(FilePath)
  Set DriverSheet = WorkBook.Worksheets("Plan1")
  '
  ColCount = DriverSheet.Usedrange.Columns.Count
  RowCount = DriverSheet.Usedrange.Rows.Count
  '
  line = 0
  For Row = 2 To RowCount
    line = line + 1
    result = ""
    For Col = 1 To ColCount
      Valor = XLS.Cells(Row, Col)
      ColName = XLS.Cells(1, Col)
      If Valor = "" Then Valor = "null"
      '
      result = result & ColName & ": " & Valor & " "     
    Next
    Log.Message("Row: " & line & " " &  result)
  Next
  '
  XLS.Quit
  Set XLS = Nothing
End Sub

 

 

I'm missing something here ?

Any help is greatly appreciated and sorry for the confusion.

 

Edit: i'm attaching an image of the logs comparison after running the Subs reading the 'Sheet_A' file.

  • We are giving you workarounds and I would hope someone has a real answer but at least these things work.

     

    My experience has been that we can't leave data cells blank and test for that.  We ended up putting a period in the "no data" cells and testing for a period rather than a blank.  

     

    It's not elegant but it works and we didn't have time to stop and figure out something pretty.  :)

4 Replies

  • cunderw's avatar
    cunderw
    Community Hero

    In my experience, the DDT driver tries to determine the cel value type by the first 10 rows in the sheet and what most of them are. Try putting 10 rows before your actual data (after the header) and put Xs in all of them.

  • Marsha_R's avatar
    Marsha_R
    Champion Level 3

    We are giving you workarounds and I would hope someone has a real answer but at least these things work.

     

    My experience has been that we can't leave data cells blank and test for that.  We ended up putting a period in the "no data" cells and testing for a period rather than a blank.  

     

    It's not elegant but it works and we didn't have time to stop and figure out something pretty.  :)

  • S_Leonardo's avatar
    S_Leonardo
    Occasional Contributor

    Thank you for all the replies, very clarifying.

    For now we are going to use some workaround like Marsha_R