Opening CSV files via Excel vba - date format change

Question

There are multiple questions concerning this problem already however none of the answers seem to solve the problem for me. The problem is this: I am having to edit a large number of .csv files so am doing this via Excel VBA. The first three columns are dates in format dd/mm/yyyy hh:mm:ss and these dates are converted to the US format MDY when opening via

    set wb1 = Workbooks.Open (Filename:=savefolder & filename, Local:=True)

The most common answer for resolving this issue surrounds the known 'feature' that Excel will open CSV with the US dateformat unless the regional settings are used via the Local:=True but I have done this and it is still not working. Can anyone advise of a setting that might need changing to get the following code to work? thanks.

    Sub cleandata()
    dim wb1 as workbook
    dim ws1 as worksheet
    dim savefolder, filename as string

    savefolder = "C:\Users\ME\Desktop\Clean Up Folder\"
    filename = dir(savefolder & "*")
    Do while len(filename) >0
           set wb1 = Workbooks.Open (Filename:=savefolder & filename, Local:=True)
           set ws1 = wb1.worksheets(1)
           'code related to what I am wanting to do to the data to 'clean it up'
           wb1.close true
           filename = dir()
    Loop
    End Sub

if settings are not doing trick you can always reformat the dates in the `code related ...` part. — Scott Holtzman, Oct 02 '17 at 16:43
It sounds like you have done yopur research but how is this not a duplicate of [this](https://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date/30609676#30609676)? — , Oct 02 '17 at 16:43
@Jeeped I had read and tried suggested solutions from several questions that refer to this issue as a result of importing CSVs, the question you link to had not come up in any of my google searches. I shall look at that and Tim William's answer on this thread and see if that resolves my issue. thanks — SMLBW, Oct 03 '17 at 08:54
@ScottHoltzman thanks for the suggestion - I was hoping that I could find a way to fix it in import but yes, will fix afterthefact if need be. — SMLBW, Oct 03 '17 at 08:55

score 0 · Answer 1 · answered Oct 02 '17 at 17:11

0

Open your file using the Data >> From Text tool: in Step 3 of the process you can specify the date format of the incoming data.

Recording a macro will give you a start on the required code.

answered Oct 02 '17 at 17:11

Tim Williams

154,628
8
97
125

score 0 · Answer 2 · answered Apr 11 '18 at 19:45

0

If you're using the TextToColumns function, you can use FieldInfo:=Array(1, 4) parameter.

I was having a similar issue and this worked for me (besides using Local:=True)

It only worked using both "solutions".

Good luck!

answered Apr 11 '18 at 19:45

Alisson Bezerra

221
2
13

score 0 · Answer 3 · answered Feb 07 '19 at 14:08

0

@SMLBW

change your system short time and long time to the "dd/mm/yyyy" format.

Im hoping this should do the trick:

answered Feb 07 '19 at 14:08

piyuw

61
1
6

Opening CSV files via Excel vba - date format change

3 Answers3