Forum Discussion

frank_vanderstr's avatar
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
    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

  • 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

      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
      'ReDim Preserve array1(i)




      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.