Tuesday, November 20, 2018

Writing Numeric Values from Excel Cells to Text File without Scientific Notation in VBA

I'm having some trouble with a VBA module in Excel. I have a script that references cells in my spreadsheet and writes the values in comma separated lines to a text file.



The way I do it boils down to:




Dim line_data(0 to 1) As String
line_data(0) = SheetNameVar.Cells(7, "C").Value
line_data(1) = SheetNameVar.Cells(8, "C").Value

Dim line As String
line = Join(line_data, ",")

TextStreamNameVar.WriteLine line



Everything works as expected, but I keep getting values written to my text file in scientific notation if they have zeroes after the decimal. For example,



3.77265350125136E-03,0.169769407556311


I have the sheet displayed as decimals with six digits after the decimal, but that doesn't seem to affect the sheet writing.



I can't seem to find any information talking about Excel VBA converting the value notation when writing to a text stream. Is there a way to prevent this? Ideally it wouldn't rely on the sheet formatting so I can display less precision there for readability. But I need the values to appear like this in my text file:



.00377265350125136,0.169769407556311



Thanks in advance for any help!

No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...