Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Problem reading value from Excel spreadsheet
Hello,
My code imports data from Excel spreadsheet. The line of code is: oExcelWS.Cells(Row, Column).Value All is fine when cell contains text or number, however one cell contains time in 24 hrs format" for example 22:35 When reading that cell value the result is not 22:35 but 0.78333... I understand that Excel does calculation in here but I need to read the original cell value without any transformation. What can be done about that? Your thoughts appreciated, Jack |
#2
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Problem reading value from Excel spreadsheet
Jack,
Use the .Text (what is displayed in the cell) property instead of the .Value (what is stored in the cell). NickHK "Jack" <notreplyto@it wrote in message ... Hello, My code imports data from Excel spreadsheet. The line of code is: oExcelWS.Cells(Row, Column).Value All is fine when cell contains text or number, however one cell contains time in 24 hrs format" for example 22:35 When reading that cell value the result is not 22:35 but 0.78333... I understand that Excel does calculation in here but I need to read the original cell value without any transformation. What can be done about that? Your thoughts appreciated, Jack |
#3
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Problem reading value from Excel spreadsheet
Thank you.
That solved the problem. I have noticed one strange thing though. One spreadsheet's column contains phone numbers. When I use .Value property the phone numbers are read properly, however when using .Text property the phone numbers are changed when the column is not wide enough to display the whole number. for example: 8562200430 is changed to one of the following numbers (depending on the column width): 8.562E+09 8.56E+09 8.6E+09 9E+09 ##### I believe it is a normal Excel behaviour but what is the reasoning behind that? Thanks, Jack "NickHK" wrote in message ... Jack, Use the .Text (what is displayed in the cell) property instead of the .Value (what is stored in the cell). NickHK "Jack" <notreplyto@it wrote in message ... Hello, My code imports data from Excel spreadsheet. The line of code is: oExcelWS.Cells(Row, Column).Value All is fine when cell contains text or number, however one cell contains time in 24 hrs format" for example 22:35 When reading that cell value the result is not 22:35 but 0.78333... I understand that Excel does calculation in here but I need to read the original cell value without any transformation. What can be done about that? Your thoughts appreciated, Jack |
#4
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Problem reading value from Excel spreadsheet
Jack,
..Text gives you what actually appears in the cell, due formatting , or in this case insufficient column width. You would need to widen (or Autofit) the column to get a meaningful result. Value is the same, no matter how it is actually displayed. NickHK "Jack" <notreplyto@it wrote in message ... Thank you. That solved the problem. I have noticed one strange thing though. One spreadsheet's column contains phone numbers. When I use .Value property the phone numbers are read properly, however when using .Text property the phone numbers are changed when the column is not wide enough to display the whole number. for example: 8562200430 is changed to one of the following numbers (depending on the column width): 8.562E+09 8.56E+09 8.6E+09 9E+09 ##### I believe it is a normal Excel behaviour but what is the reasoning behind that? Thanks, Jack "NickHK" wrote in message ... Jack, Use the .Text (what is displayed in the cell) property instead of the .Value (what is stored in the cell). NickHK "Jack" <notreplyto@it wrote in message ... Hello, My code imports data from Excel spreadsheet. The line of code is: oExcelWS.Cells(Row, Column).Value All is fine when cell contains text or number, however one cell contains time in 24 hrs format" for example 22:35 When reading that cell value the result is not 22:35 but 0.78333... I understand that Excel does calculation in here but I need to read the original cell value without any transformation. What can be done about that? Your thoughts appreciated, Jack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel OLEDB reading Chinese characters problem | Excel Programming | |||
reading data from com port into excel spreadsheet | Excel Programming | |||
Reading from a .xls spreadsheet based on a cell name | Excel Programming | |||
reading cells from a spreadsheet using VB | Excel Programming |