BUG????

[CodeSplit]

I am using excel's object and found a bug in excel's
object. After getting the value for cell number 3425
(meaning for cell number 3426) data which originally was
123-4567 is returned as 1234567. I have done everything to
test that my code is not at fault but it appears not to be
so. I am using Office 2003 and my OS is Windows 2000 with
all the latest updates. Check out the code below:

Dim m_sExcelName as String
m_sExcelName = "c:\test.xls"

Dim oExcelAnother As New
Excel.Application
Dim oInternalWorkBook As Excel.Workbook,
oInternalWorksheet As Excel.Worksheet
Set oInternalWorkBook = oExcelAnother.Workbooks.Open
(m_sExcelName)
Set oInternalWorksheet = oInternalWorkBook.Sheets(1)
Dim iRowCount as Integer
iRowCount = 5000
Dim iColCount as Integer
iColCount= 20
Dim iLoop as Integer
iLoop=0
For iRowLoop = 2 To iRowCount
For iLoop = 1 To iColCount
Dim sVal As String
sVal = CStr(excelTemp.Cells(iRowLoop, iLoop))
Next iLoop
Next iRowLoop

 

[Frank Kabel]

Hi
have you checked that there's no custom format applied to this cell?

 

[keepitcool]

What's the original datatype of that cell?

if it's a value formatted as 123-456 you'll see this behaviour.

also your code will fail if the cells containt error values.

Since you're converting it to text anyway....why not use the .Text
property (that'll give you the cell's DISPLAYED value including the
cells' numberformat rounding etc.)

Please have a carefull look at .Value / .Value2 and .Text property

btw Personally i wouldn't put a dim inside a loop!

dim sTmp$
for i=1 to x
sTmp=wks.cells(i,j).Text
next


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Guest]

thanks dude
yea that was it....
any ideas how i can convert the custom format to actual
format so that i only get 123-4567 as value and not 1234567
when i retrieve it from the cell.

 

[Frank Kabel]

Hi
you have to enter this kind of value manually or use in a helper column
a formula like
=TEXT(A1,"000-0000")

 

[Tushar Mehta]

Use the Text property of the cell object to get the formatted result.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions