CODEWITHSUNDEEP
excelvb6
Mohamed Bawaneen
Mohamed Bawaneen

Reputation: 159

format excel column to show decimal places end by zero VB

I have below vb6 code sending data to excel sheet with one number column formatted to show two decimal places ,everything is working fine except when the decimal number end with zero the excel show only one decimal place like 234.60 showing 243.6 how I can manage to show the zero 

    Private Sub Command41_Click()

    Dim cno
    Dim tex
    Dim VatAmount As Double
   Dim MyexceL As Excel.Application
   Set MyexceL = New Excel.Application
 Set MyexceL = New Excel.Application
MyexceL.Workbooks.Add
MyexceL.Cells(1, 1).Value = "THC VAT Invoices "
MyexceL.Cells(2, 3).Value = "D/O No. "
MyexceL.Cells(2, 4).Value = "Date "
MyexceL.Cells(2, 5).Value = "Clearing Agent "
MyexceL.Cells(2, 6).Value = "VAT "



tex = App.Path
tex = tex + "\delivery.mdb"
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
con.Open " Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=contianersbakatl;Data Source=ACC-PC\SQLEXPRESS"
con.Execute "DELETE FROM MASTLOT"
'If Option1.Value = True Then
Dim Fdate, sdaTE
Fdate = Format(TXTDAte.Text, "mm/dd/yyyy")
sdaTE = Format(txtdate1.Text, "mm/dd/yyyy")


Set rs = New ADODB.Recordset
rs.Open "select * from delivery where  del_date between '" & Fdate & "' and ' " & sdaTE & "' order by del_date ,DEL_sERIAL   ", con, adOpenDynamic, adLockOptimistic


 cno = 0

ccn = 2
While Not rs.EOF

cno = cno + 1
ccn = ccn + 1
MyexceL.Cells(ccn, 3).Value = rs!del_serial
MyexceL.Cells(ccn, 4).Value = CDate(rs!del_date)
MyexceL.Cells(ccn, 5).Value = rs!DEL_CLEARNAME
VatAmount = rs!del_discount * 100 / 117 * 17 / 100 

    MyexceL.Cells(ccn, 6).Value = Format(VatAmount, "#,##0.00")
    rs.MoveNext
    Wend
    MyexceL.Visible = True

    End Sub

Upvotes: 0

Views: 1872

Answers (2)

Tim Williams
Tim Williams

Reputation: 166331

You need to set the cell NumberFormat and then populate the Value: just formatting the actual value will not do it.

With MyexceL.Cells(ccn, 6)
    .NumberFormat = "#,##0.00"
    .Value = VatAmount
End With

Upvotes: 2

ashleedawg
ashleedawg

Reputation: 21639

I assume it's VatAmount that is the value in question. Is the Excel file pre-formatted (template, etc?) When you format the number on this end, that's not changing the formatting of the worksheet itself.

You'll need to changer the formatting number of decimal places displayed on the sheet, either in advance, or programmatically.

Upvotes: 1

Related Questions