csv changes date format, help please

josh.clare

Board Regular
Joined
Feb 25, 2010
Messages
144
Hello everyone,
i have a macro that deletes the top two lines of an excel and saves it as a csv. however its changing the date format from dd/mm/yyyy to mm/dd/yyyy. i would like to keep the date format as dd/mm/yyyy.
Can someone please help, i have attatched the code below,

Code:
[COLOR=#0000ff]Public[/COLOR] [COLOR=blue]Sub[/COLOR] LoopFiles()
    [COLOR=blue]Dim[/COLOR] strPath [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR], strFileName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
 
    strPath = "C:\" [COLOR=green]'change path here[/COLOR]
    strFileName = [COLOR=blue]Dir[/COLOR](strPath & "*.xl*")
 
    [COLOR=blue]Do[/COLOR] [COLOR=blue]While[/COLOR] [COLOR=blue]Len[/COLOR](strFileName) > 0
        [COLOR=blue]With[/COLOR] Workbooks.Open(strPath & strFileName)
            .Sheets(1).Rows("1:2").Delete
            .SaveAs Filename:=.Path & .Name & ".csv", FileFormat:=xlCSV
            .Close [COLOR=blue]False[/COLOR]
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
        strFileName = [COLOR=blue]Dir[/COLOR]
    [COLOR=blue]Loop[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

thanks,
josh
 
I am using the code:

Code:
Public Sub LoopFiles()
    c1 = "C:\"
    c2 = Dir(c1 & "*.xl*")
    Do While c2 <> ""
        With Workbooks.Add(c1 & c2)
            .Sheets(1).Rows("1:2").Delete
            .SaveAs c1 & Split(c2, ".")(0) & ".csv", xlCSVWindows
            .Close False
        End With
        c2 = Dir
    Loop
End Sub

I just manually opened one of the excels, deleted the top two lines, went to save as and selected csv, when prompted about mutiple sheets (even thou it only contains one) i click ok, and when prompted about containing features that csv may not support and if want to keep them like this, i click ok. and it works fine but for some reason the macro is changing the date format for example 16/01/1980 will go to 01/16/1980.
Thanks,
Josh
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I wonder if that's because of the way you are using Workbooks.Add to generate your file to save. Try changing the code to:

Code:
Public Sub LoopFiles()
    c1 = "C:\"
    c2 = Dir(c1 & "*.xl*")
    Do While c2 <> ""
        With Workbooks.Open(c1 & c2)
            .Sheets(1).Activate
            Activesheet.Rows("1:2").Delete
            .SaveAs c1 & Split(c2, ".")(0) & ".csv", xlCSVWindows
            .Close False
        End With
        c2 = Dir
    Loop
End Sub
 
Upvote 0
How many rows of data are there and when saved down to csv do you get any columns of data surrounded by double-quotes ("")? If you do get the double quotes, do you want to retain these? I am angling towards using a completely different method to generate the csvs and I need to be sure what your output needs to look like.
 
Upvote 0
row 1 is blank and row 2 has the headings (no longer required) which is why the macro deletes the top two rows. once deleted, the data goes down 1000 rows. and i cant see any data surrounded by double quotes.
Thanks,
Josh
 
Upvote 0
Do you have any cells with contents beyond 255 characters in length? This may well occur if you have lots of text in some cells.

Note: if you re using xl2007, please state this, as the 255 character limit I obliquely referred to above won't apply.
 
Upvote 0
nope, there are no cells with contents beyond 255 characters, the excels are saved as excel 97 - 2003 worksheets but i am using 2007, thanks,
josh
 
Upvote 0
Try this (much) longer routine - it doesn't use any native Excel 'save as csv' functions so hopefully won't screw up.

Code:
Sub LoopFiles()
Dim txt As String
Dim c1 As String, c2 As String
Dim fNames As Variant
Dim i As Long
i = 0
    c1 = "Y:\Work\Personal\New Folder\"
    c2 = Dir(c1 & "*.xl*")
If c2 <> "" Then
    Do
        If IsEmpty(fNames) Then
            ReDim fNames(0 To 0)
        Else
            ReDim Preserve fNames(0 To i)
        End If
        fNames(i) = c2
        i = i + 1
        c2 = Dir
    Loop Until Len(c2) = 0
    For i = 0 To UBound(fNames)
        With Workbooks.Add(c1 & fNames(i))
            With .Sheets(1).UsedRange
                txt = MakeText(.Offset(2).Resize(.Rows.Count - 2))
            End With
            WriteText c1 & Split(fNames(i), ".")(0) & ".csv", txt, True
            .Close False
        End With
        txt = vbNullString
    Next i
End If
End Sub
Function WriteText(ByVal strPathAndFilename As String, ByRef strToWrite As String, Optional ByVal blnOverWrite As Boolean = False) As Boolean
Dim i As Integer, strTemp
strTemp = Dir(strPathAndFilename)
If Len(strTemp) > 0 Then
    If blnOverWrite Then
        Kill strPathAndFilename
    Else
        WriteText = False
        Exit Function
    End If
End If
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
    Put #i, , strToWrite
Close #i
WriteText = True
End Function
 
Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = ",", Optional ByVal strNewLine As String = vbCrLf) As String
Dim i As Long, j As Long
Dim strTemp As String
 
If rng.Count = 1 Then
    MakeText = rng.Text
    Exit Function
Else
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            strTemp = strTemp & rng.Cells(i, j).Text & strDelim
        Next j
        strTemp = Left(strTemp, Len(strTemp) - 1) & strNewLine
    Next i
    strTemp = Left(strTemp, Len(strTemp) - 1)
    MakeText = strTemp
End If
End Function
 
Upvote 0

Forum statistics

Threads
1,214,952
Messages
6,122,458
Members
449,085
Latest member
ExcelError

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top