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

On May 11, 8:21*pm, BB <billb... (AT) gmail (DOT) com> wrote:
I tried this with access97 and it works fine

On May 12, 3:38*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Thanks Roger.

Is there anyone out there who can try it with Access 2007? It's
driving me crazy!

On May 12, 3:38*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
Thanks Roger.

Is there anyone out there who can try it with Access 2007? It's
driving me crazy!

BB wrote:

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

On May 13, 12:49*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Bingo! That did it! Using:

.NumberFormat = "$0.0000"
.Value = FormatNumber(pvValue, 4, vbFalse)

for the assignment did the trick. Even though FormatNumber tells it
not to use a leading zero, the .NumberFormat property on the previous
line puts in both a dollar symbol and the leading zero. Strange, but
true!

Thanks, Salad!