convert to decimal type

[enigmagirl122]

what i need is to export a series of barcodes to excel. barcodes is composed of 13-18 digits. my problem is when i export 18 digits to excel the barcodes are round off. is there a way that i can convert it to number with 0 decimal places. you can check my code below. i have 19 columns that i need to export in excel, barcode is in the 1st column the other columns are text and i don't have any problems about that.

Private Sub Exports()
Dim intresponse As Integer

intresponse = msgbox("Do you want to save your record, before exporting it?", vbYesNo)

If intresponse = vbYes Then
    Call Sayb
Else:

Dim therows As Integer
Dim thecols As Integer
Dim Gridstyle As Integer
Dim worksheetname As String
Dim x As Integer

Gridstyle = 1

Dim objxl As New Excel.Application
Dim wbxl As New Excel.Workbook
Dim wsxl As New Excel.Worksheet
Dim introw As Integer 'counter
Dim intcol As Integer  'counter

therows = MSHFlexGrid1.Rows
thecols = MSHFlexGrid1.Cols

If Not IsObject(objxl) Then
    msgbox "You need Microsoft Excel to use this function", _
       vbExclamation, "Print to Excel"
    Exit Sub
End If

On Error Resume Next

' open Excel
objxl.Visible = True
Set wbxl = objxl.Workbooks.Add
Set wsxl = objxl.ActiveSheet

' name the worksheet
With wsxl
    If Not worksheetname = "" Then
        .Name = worksheetname
    End If
End With
    
' fill worksheet
For introw = 1 To therows
    For intcol = 1 To thecols
        With MSHFlexGrid1
            wsxl.Cells(introw + 14, intcol).Value = _
               .TextMatrix(introw - 1, intcol - 1) & " "
        End With
    Next
Next

' format the look
For intcol = 1 To thecols
    wsxl.Columns(intcol).AutoFit
    'wsxl.Columns(intcol).AutoFormat (1)
   ' wsxl.Range("a1", Right(wsxl.Columns(thecols).AddressLocal, _
    '     1) & therows).AutoFormat Gridstyle
Next
     

'headers

wsxl.PageSetup.LeftFooter = "&D"
wsxl.PageSetup.RightFooter = "Page &P of &N"
'wsxl.Cells.Font.FontStyle [tahoma]

wsxl.Cells.Font.Size = 11

'format
wsxl.Range("c1").Value = "GS1 Philippines, Inc."
wsxl.Range("c2").Value = "(Formerly Philippine Article Council Inc.)"
wsxl.Range("c3").Value = "#20 San Rafael St. Bo. Kapitolyo, Pasig City 1603"
wsxl.Range("c4").Value = "Tel. No. (02) 637-0897 to 98, 637-0557         Fax No. (02) 636-5207/631-4631"
wsxl.Range("c5").Value = "E-mail: [email protected],   Website: www.gs1ph.org"
wsxl.Range("a7").Value = "PLEASE FILL UP THIS FORM COMPLETELY AND CLEARLY TO AVOID DELAYS"
wsxl.Range("c9").Value = "*Company Name:"
wsxl.Range("c10").Value = "*Fax Number:"
wsxl.Range("c11").Value = "*Telephone No.:"
wsxl.Range("c12").Value = "*Contact Person:"
wsxl.Range("c13").Value = "*Date Submitted:"
wsxl.Range("a14").Value = "Please be guided with this format (EXCEL or RDBMS Type"
wsxl.Range("i9").Value = "*Required"
wsxl.Range("j9").Value = "Upon receiving your Prefix Number:"
wsxl.Range("j10").Value = "1. Assign the item number per stock keeping unit (SKU)/item."
wsxl.Range("j11").Value = "2. Compute for the check digit."
wsxl.Range("j12").Value = "3. Fill up this form completely and submit this GS1PI (Formerly PANCI) for approval."
wsxl.Range("j13").Value = "***Note: Please wait for GS1 approval before printing the barcode symbol."
wsxl.Range("k1").Value = "***FOR GS1PI USE ONLY"
wsxl.Range("k2").Value = "CHECKED BY: "
wsxl.Range("p2").Value = "DATE: "
wsxl.Range("k3").Value = "ENCODED BY: "
wsxl.Range("p3").Value = "DATE: "
wsxl.Range("k4").Value = "REMARKS: "

wsxl.Range("k1", "q1").AutoFormat Gridstyle
wsxl.Range("b15", "q15").AutoFormat Gridstyle
  
'wsxl.Columns("A").Delete
wsxl.Columns("D").Delete
wsxl.Columns("Q").clear
wsxl.Columns("R").clear
wsxl.Range("b15", "b50015").NumberFormat = 0
wsxl.PageSetup.Zoom = False
wsxl.PageSetup.FitToPagesTall = 1
wsxl.PageSetup.FitToPagesWide = 1
wsxl.PageSetup.Orientation = xlLandscape
'wsxl.PageSetup.PaperSize = xlPaperA4
wsxl.PageSetup.PaperSize = xlPaperLegal
End If

End Sub

here is an example if i export 148098765430000009 from mshflexgrid to excel the outcome is 1.48099E+17 which should be 148098765430000009.

thanks!

[emartinho]

You need to set the format of the cell that will contain the barcode to "Text". The usual
format is "General" and this automatically changes the display of large numbers to use e
notation.

Use the NumberFormat property with value "@" to set the affected cell to "text" format
before putting the Barcode in it.

Cheers,
-Elio

[jggtz]

Try sending it with a prefixed ' (single quote)

[westconn1]

as all the other columns are text, you could just set the format for the entire range, before transferring any data

With wsxl
    If Not worksheetname = "" Then
        .Name = worksheetname
    End If
    .range(.cells( 1, 1), .cells(therows, thecols)).numberformat = "@"  ' set format of target range to text
End With
 
For introw = 1 To therows
    For intcol = 1 To thecols
        With MSHFlexGrid1
            wsxl.Cells(introw + 14, intcol).Value = _
               cstr(.TextMatrix(introw - 1, intcol - 1)) & " "    ' convert all values to string, though only really required for column 1
        End With
    Next
Next

[enigmagirl122]

hit it didn't work still 1.48099E+17 and if i double click the cell it will change to 148098765430000000 which is wrong it should be 148098765430000009.

[enigmagirl122]

How can i stop it from rounding off?

[enigmagirl122]

13 and 14 digit barcodes is working fine except for 18 digits it keeps rounding off. how can i stop this.


thanks!

[westconn1]

did you check the format of the cell?

as long as i work with strings i have no problem displaying the correct value
i get the double click effect if the cell is formatted general or other number

[anhn]

With numeric values, Excel cells can handle with the precision of Double data type (maximum 15 accurate digits). This is a big problem for novice user who doesn't aware of.
I have seen people enter 16 consecutive digits of credit card into a cell then press enter and didn't know that the last digit was round off (even with format "0000000000000000", 16 zero's).

There is only one way to deal with this is to convert that numeric value to Text (String) and a safe way is to precede the value with a single quote ('), no worry abount the cell format.

If intcol = 2 then
   wsxl.Cells(introw + 14, intcol).Value = "'" & .TextMatrix(introw - 1, intcol - 1)
Else
   wsxl.Cells(introw + 14, intcol).Value = .TextMatrix(introw - 1, intcol - 1)
End If