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 wi...
  • 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!)

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.

      • Colin_McCrae's avatar
        Colin_McCrae
        Community Hero
        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!)