Automation:insert number to Excel problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Artem Omelianchuk - 26 Jan 2006 11:38 GMT

Hi.
I use Range to insert some numbers to the sheet. All works find. But I have
one problem.

Excel converts numbers like 2.76 to 1 febrary 1976. How to avoid this?

Reply to this Message

Crowbar - 26 Jan 2006 14:52 GMT

It sounds like the cells format is incorrect

Have you tried changing the cell format to number ?

Reply to this Message

Artem Omelianchuk - 26 Jan 2006 16:00 GMT

How can I do this,and for what have i to change it?

> It sounds like the cells format is incorrect
>
> Have you tried changing the cell format to number ?

Reply to this Message

Bernie Deitrick - 26 Jan 2006 14:54 GMT

Artem,

If your locallized date setting allows the use of . as the date delimiter, then Excel could
interpret "2.76" (a string) as a date.

Beyond that, post your code.

HTH,
Bernie
MS Excel MVP

> Hi.
> I use Range to insert some numbers to the sheet. All works find. But I have
> one problem.
>
> Excel converts numbers like 2.76 to 1 febrary 1976. How to avoid this?

Reply to this Message

Artem Omelianchuk - 26 Jan 2006 15:45 GMT

Thank you for interest.
I understand this,but how can i avoid tis behaviour?

I can't post my code today,i make this tomorrow. But in general I Get Range
from the Worksheet and add SafeArray there.
Number represented by string.

> Artem,
>
[quoted text clipped - 12 lines]
> >
> > Excel converts numbers like 2.76 to 1 febrary 1976. How to avoid this?

Reply to this Message

Bernie Deitrick - 26 Jan 2006 15:57 GMT

Artem,

Check your "Regional and language options" from the control panel in windows, specifically, the
regional options for short date.

How to avoid this in the code depends on how you are assigning your cell values. Perhaps along the
lines of:

With Range("A1")
.NumberFormat = "0.00"
.Value = CDbl(SafeArray(i))
End With

HTH,
Bernie
MS Excel MVP

> Thank you for interest.
> I understand this,but how can i avoid tis behaviour?
[quoted text clipped - 19 lines]
>> >
>> > Excel converts numbers like 2.76 to 1 febrary 1976. How to avoid this?

Reply to this Message

Artem Omelianchuk - 27 Jan 2006 05:11 GMT

Hi,
Here are my code:

CString start;
start.Format(_T("A%d"),m_rowsCount+2);

CRange oRange;
oRange = m_sheet.get_Range(COleVariant(start), m_covOptional);
oRange =
oRange.get_Resize(COleVariant((short)1),COleVariant((short)m_numberOfFields));
oRange.put_NumberFormat(COleVariant(_T("#,##0.00")) );
oRange.put_NumberFormatLocal(COleVariant(_T("#,##0.00")) );
oRange.put_Value(sa);

,where sa is COleSafeArray.

But when i try to change number format I get COleDispatchException - "Can't
change number format of class Range".
What I'm doing wrong.

> Artem,
>
[quoted text clipped - 36 lines]
> >> >
> >> > Excel converts numbers like 2.76 to 1 febrary 1976. How to avoid this?

Reply to this Message

Artem Omelianchuk - 27 Jan 2006 07:01 GMT

I make some research and find, that this behavior because of
UseSystemSeparators property. So now I turn off this property and all works
fine.
Thanks.

Reply to this Message