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:
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.
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.