excelvbacsvscientific-notation

VBA converts number into scientific notation when writing to csv file


I have an Excel worksheet that contains data such as shown below:

A B C D E
Paul Johnson New York 0 FALSE
CoordX 90 0.08348590975457270

I need to write this to a csv file.

The .SaveAs method isn't working for me as this would create 2 trailing commas for row 2.

My approach therefore is:
- For each row in the worksheet I determine the last column containing data;
- Then I create a string of all cells in that row;
- That string is written to a file.

The relevant part of the code looks like:

fileLine = sCat(Range(ws.Cells(rLoop, 1), ws.Cells(rLoop, maxCols + 1)), ",")
Print #fileNumber, fileLine

where sCat is the following function:

Function sCat(vP As Variant, delim As String) As String

    ' Concatenate all cells in a range, delimited by delim.
    Dim v
    Dim delim2 As String
    
    For Each v In vP
        sCat = sCat & delim2 & v
        delim2 = delim
    Next v
End Function

The data in my output csv file looks like:

Output file

Thus the number 0.08348590975457270 end up as 8.348590975457270E-2. How can I avoid the scientific notation appearing in the csv file (without having to play around with the formatting of the cells in the worksheet)?

I was advised to try:

fileLine = sCat(Application.Trim(Range(ws.Cells(rLoop, 1), ws.Cells(rLoop, maxCols + 1))), ",")

but this only partially worked. Most numbers didn't get converted into scientific notation anymore. However, there were a couple of numbers that were still in scientific notation. Quite erratic behaviour.


Solution

  • Add a numeric check and formatting:

    Function sCat(vP As Variant, delim As String) As String
        ' Concatenate all cells in a range, delimited by delim.
        Dim v
        Dim delim2 As String
        
        For Each v In vP
            If IsNumeric(v) Then
                If v <> Int(v) Then
                    'check for non-integer numbers only
                    v = Format(v, "0.0#################################")
                End If
            End If
            sCat = sCat & delim2 & v
            delim2 = delim
        Next v
    End Function
    
    

    Format(v, "0.##################################") forces the full set of digits.