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.
