Opening csv file with macro causes date format to change

[NickGraham]

Hi, I'm using some code I found on this forum to open a csv file. One of the columns contains dates and when opening the file using the macro the date format is changed from dd/mm/yyyy to mm/dd/yyyy but only if the date is on or before the 12th month. If the file is opened manually the date format stays as dd/mm/yyyy

The code I am using is:

Sub OpenCompletedWorkItem()
'opens the CompletedWorkItem file
Dim DateToOpen As String
DateToOpen = Format(Date, "yyyy-mm-dd")
'searches for all the CompletedWorkItem files with todays date
 With Application.FileSearch
        .NewSearch
        .LookIn = "J:\Dept\CSU TL\Batch Tracker\Ciboodle Data"
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "CompleteWorkItem_" & DateToOpen & "*"
        .Execute
        
        'counts the number of CompletedWorkItem files with todays date
        i = .FoundFiles.Count
        
        'opens the most recent CompletedWorkItem file
         Set wb = Workbooks.Open(.FoundFiles(i))
    End With
End Sub

It looks to me like Excel is defaulting to the US date format but I don't know how to instruct it that the date format is UK. Does anyone know how to fix this?

 

[Richard Schollar]

Don't you just hate the American date format? Causes no end of problems. The only way I know of to get csv data into Excel programmatically with the correct date format is to use a QueryTable to import. Otherwise you will have to process the dates after import to correct them.

 

[j844929]

Hi,

This is a bit of a delayed reply, but I couldn't ignore the problem you've had, as I've had it myself in the past. There is a really easy way to set the date formats correctly.

If you use the following style of VB to open a CSV file, then the very last bit will set the dates correctly:

Workbooks.Open Filename:= _
"C:\My Folder\" & FileIWantToOpen & "", _
Local:=True

":=Local" is the key...

That should work!

 

[j844929]

Sorry, "Local:=True" - THAT's the key, not ":=Local"