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 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 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 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 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, NYNext: Whats with all the SPAM suddenly?