vba excel ado stream for extract to utf8 : non scientifc format for numbers

685 Views Asked by At

I have an issue : I use an ADO Stream object to create an UTF8 - CSV file from datasheet from EXCEL 2013

In my datasheet i have big numbers which are automatically set in Scientific format : 0,0193370165745856 set as 1,93370165745856E-02 in my csv file.

I tried to force this value to a non scientific format or text format with Selection.NumberFormat = "General" or Selection.NumberFormat = "@" but in the end there is always the same result Before create the ADO STREAM object in my Excel file the format is well forced.

My ADO STREAM Object function :

'==================================================
Public Function Zone2CSV_UTF8(fichier As String, Onglet As String, Zone As String)
Dim nb_l, nb_c, c, l As Integer
Dim ligne As String
Dim objStream As ADODB.Stream

' init stream
Set objStream = New ADODB.Stream
objStream.Open
objStream.Charset = "utf-8"
objStream.Type = adTypeText
objStream.LineSeparator = adCRLF
objStream.Position = 0


nb_c = Worksheets(Onglet).UsedRange.Columns.Count
nb_l = Worksheets(Onglet).UsedRange.Rows.Count

With Worksheets(Onglet).Range(Zone)
 For l = 1 To nb_l
   ligne = ""
   For c = 1 To nb_c
    ligne = ligne & Replace(Replace(Replace(.Cells(l, c), "\r", " "), "\n", " "), ";", ".") & ";"
    If c = nb_c Then
    ligne = Left(ligne, Len(ligne) - 1)
    End If
   Next c
  objStream.WriteText ligne, adWriteLine
 Next l

End With

objStream.SaveToFile fichier, adSaveCreateOverWrite
' close up and return
objStream.Close
Set objStream = Nothing
Zone2CSV_UTF8 = RemoveBOM(fichier)

Thank you for helping me.

0

There are 0 best solutions below