Prev: Hyperion FDM/ FDQM , New york city, NY
Next: Whats with all the SPAM suddenly?
From: BB on 11 May 2010 22:21
I've got an Access 2007 application that uses automation to create an
Excel 2007 Workbook. Everything works great except that it changes
the value of longer decimals. If I send it a value of 0.0599, it
shows up in Excel as .0600. So it's rounding but keeping the correct
number of decimal places.

Here's the relevant code. Any idea what I'm doing wrong?

Private Sub SetCellValue(pobjWB As Excel.Workbook, psRange As String,
pvValue As Variant, Optional piDecimals As Integer = 2)

Dim sDecimals As String

With pobjWB.ActiveSheet
With .Range(psRange)

sDecimals = Space(0)
If Not IsMissing(piDecimals) Then
If piDecimals > 0 Then
sDecimals = "." & String(piDecimals, "0")
End If
End If

.NumberFormat = "0" & sDecimals

.Value = pvValue
End With
End With

End Sub

And the call would be something like:

SetCellValue objWB, "G3", 0.0599, 4
From: Roger on 12 May 2010 04:38
On May 11, 8:21 pm, BB <billb...(a)gmail.com> wrote:
> I've got an Access 2007 application that uses automation to create an
> Excel 2007 Workbook.  Everything works great except that it changes
> the value of longer decimals.  If I send it a value of 0.0599, it
> shows up in Excel as .0600.  So it's rounding but keeping the correct
> number of decimal places.
>
> Here's the relevant code.  Any idea what I'm doing wrong?
>
> Private Sub SetCellValue(pobjWB As Excel.Workbook, psRange As String,
> pvValue As Variant, Optional piDecimals As Integer = 2)
>
>     Dim sDecimals As String
>
>     With pobjWB.ActiveSheet
>         With .Range(psRange)
>
>                 sDecimals = Space(0)
>                 If Not IsMissing(piDecimals) Then
>                     If piDecimals > 0 Then
>                         sDecimals = "." & String(piDecimals, "0")
>                     End If
>                 End If
>
>                 .NumberFormat = "0" & sDecimals
>
>                 .Value = pvValue
>         End With
>     End With
>
> End Sub
>
> And the call would be something like:
>
> SetCellValue objWB, "G3", 0.0599, 4

I tried this with access97 and it works fine
From: BB on 13 May 2010 11:46
On May 12, 3:38 am, Roger <lesperan...(a)natpro.com> wrote:
> I tried this with access97 and it works fine

Thanks Roger.

Is there anyone out there who can try it with Access 2007? It's
driving me crazy!
From: BB on 13 May 2010 11:46
On May 12, 3:38 am, Roger <lesperan...(a)natpro.com> wrote:
> I tried this with access97 and it works fine

Thanks Roger.

Is there anyone out there who can try it with Access 2007? It's
driving me crazy!
From: Salad on 13 May 2010 13:49
BB wrote:

> On May 12, 3:38 am, Roger <lesperan...(a)natpro.com> wrote:
>
>>I tried this with access97 and it works fine
>
>
> Thanks Roger.
>
> Is there anyone out there who can try it with Access 2007? It's
> driving me crazy!

Maybe this will work. Then again, maybe not
x = 0.5999
? formatnumber(x,2)
0.60
? formatnumber(x,4) 'default leading 0
0.5999
? formatnumber(x,4,0) 'no leading 0. -1 to force leading 0
..5999

There are newsgroups related to Excel. Maybe try one of them like
microsoft.public.excel.worksheet.functions
 |  Next  |  Last
Pages: 1 2
Prev: Hyperion FDM/ FDQM , New york city, NY
Next: Whats with all the SPAM suddenly?