CODEWITHSUNDEEP
excelvbadoubledecimal
DeltaKilo
DeltaKilo

Reputation: 123

VBA: Leading zeros missing in double variables

I've written a VBA script to export data from a table to a CSV file in a specific structure, required as input to other software. The script works, except decimal values are missing their leading zero if < 1, e.g. 1.234 writes as expected, however 0.123 writes as .123.

I do not want to write it as a string as every method I have tried, e.g. Format(Day(myDate), "00"), creates quotes around the value in the CSV file, which is incompatible with the software this file is needed for. Is there a way to force the variable to retain the leading zero, or to write as a string without including quotes?

Exctract of code is below, where cellValue1 is variable holding the value.

Sub test_export()

Dim txtFile As String, rng1 As Range, rng2 As Range, cellValue1 As Double, i As Integer
Dim myDate As Date, yyyy As Integer, mm As Integer, dd As Integer, hh As Integer, min As Integer, ss As Integer

txtFile = Application.DefaultFilePath & "\test.csv"

Set rng1 = Worksheets("OUTPUT").Range("B8:B103")
Set rng2 = Worksheets("OUTPUT").Range("G8:G103")

Open txtFile For Output As #1

For i = 1 To rng1.Rows.Count

myDate = rng1.Cells(i, 1).Value

yyyy = year(myDate)
mm = month(myDate)
dd = day(myDate)
hh = hour(myDate)
min = minute(myDate)
ss = Second(myDate)

cellValue1 = rng2.Cells(i, 1).Value

Write #1, yyyy, mm, dd, hh, min, ss, cellValue1

Next i

Close #1

End Sub

Upvotes: 0

Views: 373

Answers (3)

TinMan
TinMan

Reputation: 7759

Format() can be used to simplify the output:

Open txtFile For Output As #1

For i = 1 To rng1.Rows.Count
    Print #1, Format(rng2.Value, "yyyy,mm,dd,hh,n,ss,") & rng2.Value2
Next i

Close #1

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

The Write# statement is inserting commas for you. While that's convenient, it's also inserting the " string delimiters that you don't want.

Use a Print# statement instead, and build your comma-separated string line yourself: it'll be written to the file exactly as you want it.

Dim rowValues(0 To 6)
rowValues(0) = DateTime.Year(myDate)
rowValues(1) = DateTime.Month(myDate)
rowValues(2) = DateTime.Day(myDate)
rowValues(3) = DateTime.Hour(myDate)
rowValues(4) = DateTime.Minute(myDate)
rowValues(5) = DateTime.Second(myDate)
rowValues(6) = rng2.Cells(i, 1).Value2 'Range.Value2 reads a Date value into a Double


Print #1, Join(rowValues, ",")

Upvotes: 3

Glenn G
Glenn G

Reputation: 667

Try

Write #1, yyyy, mm, dd, hh, min, ss, Format(cellValue1,"#0.000#")

Edited Response

To get it to output without the " try this

Print #1, yyyy, mm, dd, hh, min, ss, Format(cellValue1,"#0.000#")

Upvotes: 0

Related Questions