Export of excel to csv

[wagdownunder]

Hi

I have downloaded this macro to export data from excel to a csv file. Everything works fine except when the date and time is exported. It seems that the date and time formats of excel is ignored are there a way of incoporating it in this macro?

any feedback would be appreciated

Daniel

Sub csvfile()
Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\Quanta Data_" & Format(Now, "YYYYMMDD_HHMMSS") & ".csv", True)
For r = 26 To Range("A65536").End(xlUp).Row
s = ""
c = 1
While Not IsEmpty(Cells(r, c))
s = s & Cells(r, c) & ","
c = c + 1
Wend
a.writeline s 'write line
Next r

End Sub

 

[diddi]

try this:

Sub csvfile()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String
    dim mn As String, Temp as string
    Set fs = CreateObject("Scripting.FileSystemObject")
    temp="c:\Quanta Data_" & Format(Now, "YYYYMMDD_HHMMSS") & ".csv"
    Set a = fs.CreateTextFile(Temp , True)
    For r = 26 To Range("A65536").End(xlUp).Row
        s = ""
        c = 1
        While Not IsEmpty(Cells(r, c))
            s = s & Cells(r, c) & ","
            c = c + 1
        Wend
        a.writeline s 'write line
    Next r
End Sub

 

[wagdownunder]

Hi

Thanks for the quick reply. However it does not seem to resolve the problem. Column 4 and 5 contains the date and time in the excel spreadsheet with the following formats.

dd/mm/yyyy
hh:mm

The date format in the CSV file is the same, but the time format has been changed to a value, see example.

13:34 (xls) 0.565277778 (csv)

 

[diddi]

Sub csvfile()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String
    dim mn As String, Temp as string
    Set fs = CreateObject("Scripting.FileSystemObject")
    temp="c:\Quanta Data_" & Format(Now, "YYYYMMDD_HHMMSS") & ".csv"
    Set a = fs.CreateTextFile(Temp , True)
    For r = 26 To Range("A65536").End(xlUp).Row
        s = ""
        c = 1
        While Not IsEmpty(Cells(r, c))
            if c=4 then s = s & format(Cells(r, c),"dd/mm/yyyy") & ","
            elseif c=5 then s = s & format(Cells(r, c),"hh:mm") & ","
            else
                s = s & Cells(r, c) & ","
            end if
            c = c + 1
        Wend
        a.writeline s 'write line
    Next r
End Sub

 

[wagdownunder]

diddi

Thanks for the reply, it seems that there is an error with the if, else and end if statements

 

[diddi]

try this:

Sub csvfile()
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String
    Dim mn As String, Temp As String
    Set fs = CreateObject("Scripting.FileSystemObject")
    Temp = "c:\Quanta Data_" & Format(Now, "YYYYMMDD_HHMMSS") & ".csv"
    Set a = fs.CreateTextFile(Temp, True)
    For r = 26 To Range("A65536").End(xlUp).Row
        s = ""
        c = 1
        While Not IsEmpty(Cells(r, c))
            If c = 4 Then
                s = s & Format(Cells(r, c), "dd/mm/yyyy") & ","
            ElseIf c = 5 Then
                s = s & Format(Cells(r, c), "hh:mm") & ","
            Else
                s = s & Cells(r, c) & ","
            End If
            c = c + 1
        Wend
        a.writeline s 'write line
    Next r
End Sub

 

[wagdownunder]

diddi

Thanks for your time, it works excellent!