Forum Discussion

frank_vanderstr's avatar
frank_vanderstr
Contributor
9 years ago

Is it Possible To Format the Workbook Cells A Test Script Reads In?

I am currently attempting to read in values from an excel file and compare them with values that appear on my test site.

However, since they are monetary values, the default value in the workbook will not match the value on the test site EX: Workbook cell value = 25, Test Site Value = 25.00.

 

I have tried changing the format in the Excel document to change the values to the correct format I want, but when my script reads the values in, they are unformatted.

 

Is there a function for workbooks to set the format of data I read in? I have tried searching but I have not been able to find the functions for workbooks/excel sheets that are supported through TestComplete.

  • Colin_McCrae's avatar
    Colin_McCrae
    9 years ago

    joseph_michaud ... handy to know. I wasn't aware of the ".text" property.

     

    (Everything on my spreadsheets - and I use a LOT of them - comes with a letter prefix to indicate what the data should be used for. So everything is treated as strings. Keeps it simple!)

5 Replies

Sort By
  • Colin_McCrae's avatar
    Colin_McCrae
    Community Hero
    9 years ago

    This is a problem with the MS Excel drivers I think.

     

    Do your columns have headers? Or are there any blank rows?

     

    I seem to remember, it looks at some cells in a column (near the top I suspect) to try and determine what kind of data is in them. It then applies this to ALL cells in that column, regardless of individual cell formatting.

     

    If you remove all headers and blank lines, and have only monetary values from the top down, it should start working. As if by magic!

     

    (Pretty sure this is the case .... someone please correct me if not!)

    • frank_vanderstr's avatar
      frank_vanderstr
      Contributor
      9 years ago

      The whole sheet I am reading from is formatted to number which is how I want the data to look like when I read it in. I think the problem is when it reads a cell, it looks at the string/value in the top box that displays your cell value and allows you to change it.

       

      Maybe I can better explain what I hope to discover by posting my code for this section.

       

      Set objExcel = CreateObject("Excel.Application")

      Set objWorkBook = objExcel.WorkBooks.Open("Fees Test Cases.xlsx")
      Set xlsheet = objExcel.ActiveWorkBook.WorkSheets("Expected Results")

      'get all the expected results for the row passed into the function
      Dim i
      ReDim array1(26)

      For i = 1 to valcount
      Dim cellval
      ReDim Preserve array1(i)
      array1(i) = xlsheet.Cells(row,i+1).Value
      Log.Message(array1(i))
      'ReDim Preserve array1(i)

      Next

      objWorkBook.Close
      objExcel.Quit

       

      Now the value I read in from array1(i) = xlsheet.Cells(row,i+1).Value is always whats the value of the cell before formatting. So although looking at the document, I see 25.00 thanks for the formatting, the value on the cell when I click on it is 25.

       

      If I can somehow format the value before I set it to array1(i), or even grab the displayed string instead of the cell value that would be great, but I can't tell if there are any supported methods for it.