csv changes date format, help please

[josh.clare]

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,

[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

 

[snb_]

Try:

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

 

[josh.clare]

Hey snb
the date is still changing from dd/mm/yyyy to mm/dd/yyyy :s
Thanks,
Josh

 

[josh.clare]

anyone please?

 

[snb_]

Nest Try

Public Sub LoopFiles()
    c1="C:\"
    c2 = Dir(c1 & "*.xl*")
 
    Do While c2<>""
        With Workbooks.Add(c1 &  c2)
            .Sheets(1).Rows("1:2").Delete
            for each cl in sheets(1).specialcells(2,1)
               if isdate(cl) then cl=format(cl.Value," dd-mm-yyyy ")
            next
            .SaveAs c1 & split(c2,".")(0) & ".csv", xlCSVWindows
            .Close False
        End With
        c2 = Dir
    Loop
End Sub

 

[josh.clare]

error and debugger it says c1 = empty in line
For Each cl In Sheets(1).SpecialCells(2, 1)
Thanks,
Josh

 

[josh.clare]

any ideas?
Josh

 

[Richard Schollar]

Hi Josh

It *shouldn't* be changing the format of the date when it actually gets saved to the csv, what is most likely happening is that to check what has been saved you are re-opening the file in Excel and when you do this Excel 'helpfully' converts what it sees as date values to their equivalent mmddyy equivalent.

Once you have saved the csv file down, try opening the csv file from Notepad - you can review what has actually been saved here.

Why do you want to save the file as a csv? Is it to limit the file size, or because the file needs to be used by an external system?

 

[josh.clare]

just opened one of the csvs in notepad and it is still in the wrong date format mm/dd/yyyy. it needs to be a csv file as it is being used by an external system.
Thanks,
Josh

 

[Richard Schollar]

Are you using your original code? Because it seems to work for me as I suggested in my previous post.