none
Issue with dates RRS feed

  • Question

  • Hi everyone

    I have the following code that exports a sheet ("Data") in Excel as csv onto my file directory:

    Sub Export()

    Dim FirstCol As Integer
    Dim LastCol As Integer
    Dim C As Integer
    Dim FirstRow
    Dim LastRow
    Dim R
    Dim data
    Dim ExpRng As Range
    Dim wks As Worksheet: Set wks = Sheets("Data")

    Range("A1").Select
       
        Set ExpRng = Range(wks.Cells(1, 1), wks.Cells(1, 1).SpecialCells(xlLastCell))
        FirstCol = ExpRng.Columns(1).Column
        LastCol = FirstCol + ExpRng.Columns.Count - 1
        FirstRow = ExpRng.Rows(1).Row
        LastRow = FirstRow + ExpRng.Rows.Count - 1
          
        Open ThisWorkbook.Path & "\Exported Data " & Format(DateSerial(Year(Date), Month(Date), 1) - 1, "mmmm yyyy") _
        & " " & ".csv" For Output As #1
               
            For R = FirstRow To LastRow
                For C = FirstCol To LastCol
                    data = ExpRng.Cells(R, C).Value
                    If data = "" Then data = ""
                    If IsNumeric(data) Then data = Val(data)
                    If C <> LastCol Then
                        Write #1, data;
                    Else
                        Write #1, data
                    End If
                Next C
            Next R
        Close #1
           
    End Sub

    The code works fine except that if a column contains a date then it is exported with # either side of the date ie rather than 01/01/2013 it appears as #2013-01-01#

    The date originates from SQL Server as a datetime field which I convert to a DATE field with the following before exporting: 

    Sub ChangeDates()

    With Worksheets("Data")

            .Range("A:A").NumberFormat = "dd/mm/yyyy"
            .Range("D:D").NumberFormat = "dd/mm/yyyy"
            .Range("AV:AV").NumberFormat = "dd/mm/yyyy"
                  
    End With

    Sheets("Data").Columns.AutoFit

    End Sub

    Any help would be greatly appreciated.

    Thanks in advance.

    BO

    Wednesday, June 5, 2013 5:22 PM

All replies