Save columns to csv VBA

Question

I have the following columns:

Time    NumberOfTasks   TimeOfCapture
0:00                     29-07-15 9:15
0:01                     29-07-15 9:15
0:02                     29-07-15 9:15
0:03                     29-07-15 9:15
0:04                     29-07-15 9:15
0:05                     29-07-15 9:15
0:06                     29-07-15 9:15
0:07    1                29-07-15 9:15

I would like to write these columns to a csv file and save it on my disk. I do the following:

For i = 1 To LastRow
    For j = 1 To 3
        If j = 3 Then
            CellData = CellData + Trim(Worksheets("Output").Cells(i, j).Text)
            Else
                CellData = CellData + Trim(Worksheets("Output").Cells(i, j).Text) + ","
                End If
            Next j
        Write #2, CellData
        CellData = ""
    Next i
    Close #2
    MsgBox ("Done")

The output that I get from this in my csv file:

Time       NumberOfTasks    TimeOfCapture
0                           29-7-2015 9:15
6,94E+10                    29-7-2015 9:15
1,39E+11                    29-7-2015 9:15

What is wrong with the first time column?

can you not just save the sheet as a CSV? rather than processing them? — 99moorem, Jul 30 '15 at 12:44
@99moorem that is not possible because this saving is part of another code... and I want that it saves automatically — F1990, Jul 30 '15 at 12:45
Use `.Cells(i, j).Text` to get the displayed value you see in a cell. The `.Value` is a numerical decimal, e.g. `0:01` is actually `0.000694444 `. — , Jul 30 '15 at 12:47
See http://stackoverflow.com/questions/21568837/excel-macro-export-sheet-to-csv-and-browse-for-save-directory and http://stackoverflow.com/questions/10551353/saving-excel-worksheet-to-csv-files-with-filenameworksheet-name-using-vb — 99moorem, Jul 30 '15 at 12:47
@Jeeped many thank's that is indeed the solution! Great help! — F1990, Jul 30 '15 at 13:20

mielk · Answer 1 · 2015-07-30T12:44:46.430

0

In VBA & is the operator for combining strings (not + like in other languages). Try to change your code like that:

For i = 1 To LastRow

    For j = 1 To 3

        If j = 1 Then
            CellData = Format(CellData, "HH:MM") + Trim(Worksheets("Output").Cells(i, j).Value) + ","
        ElseIf j = 3 Then
            CellData = CellData + Trim(Worksheets("Output").Cells(i, j).Value)
        Else
            CellData = CellData + Trim(Worksheets("Output").Cells(i, j).Value) + ","
        End If
    Next j

    Write #2, CellData
    CellData = ""

Next i

Close #2
MsgBox ("Done")

edited Jul 30 '15 at 12:44

answered Jul 30 '15 at 12:41

mielk

3,890
12
19

it does not matter if I use the & operator instead of the + . I think it has something to do with the format of the column, it is a time hour:minutes.... – F1990 Jul 30 '15 at 12:42
You are right, time value is converted to number. I have modified code to append value from column 1 into HH:MM format, maybe it help. – mielk Jul 30 '15 at 12:45
It saves as a scientific number with two decimals – F1990 Jul 30 '15 at 12:48

score 0 · Accepted Answer · answered Jul 30 '15 at 13:20

The following works:

For i = 1 To LastRow
    For j = 1 To 3
        If j = 3 Then
            CellData = CellData + Trim(Worksheets("Output").Cells(i, j).Text)
            Else
                CellData = CellData + Trim(Worksheets("Output").Cells(i, j).Text) & ","
                End If
            Next j
        Write #2, CellData
        CellData = ""
    Next i
    Close #2
    MsgBox ("Done")

Save columns to csv VBA

2 Answers2