Help required: VBA ignoring regional date settings - using US format as default.

Hi all,

I am importing a series of dates from a .csv file which look like "DD.MM.YYYY" (this is an export from a piece of software I do not have any power over and so is not negotiable) and I require the date format "DD/MM/YYYY". I do not know what format the dates are imported in.

To convert from one to the other I have used:

Sheets("Import").Select
Columns("D:D").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.NumberFormat = "dd/mm/yy;@"

Which came from the 'Record Macro' function. However, this returns correctly formatted dates if and only if the "DD" value is less than or equal to 12. I have read a few forum posts and I understand that this is because VBA defaults to US date settings - if I perform these exact changes manually I obtain the correctly formatted dates for all entries.

I would very much appreciate your help with this issue. Please let me know if I can provide any additional information.

Thanks in advance,

Josh.

Hi Norie, and thanks for your reply.

I am converting the dates as shown in the code above. When imported, the dates look like "01.01.2013" and "30.01.2013" but afterwards they look like "01/01/2013" (right aligned, works as a date in the rest of my spreadsheet) and "30/01/2013" (left aligned, does not work in the rest of my spreadsheet) respectively. Would a screen grab help?

In what way could I 'deal' with the dates when importing?

Regards,

Josh.

Josh

How are you getting the date into Excel from the CSV?

The reason I ask is because if you were importing via the text wizard you could use that to deal with the dates.

You might still be able to deal with them all in one go, with no code, just select the column of dates, goto Data>Text to columns and on the 3rd step choose YMD for the Column data format.

Of course if you want code.

With Worksheets("Import")
    .Range("D:D").TextToColumns Destination:=.Range("D1"), DataType:=xlDelimited,  FieldInfo :=Array(1, 4)
End With

Note, this is for the whole column and assumes no header, it would probably be an idea to adjust it so it's limited to the range with dates.

Hi Norie,

I am importing the csv using VBA. The segments of code I have provided are actually part of a larger 'update the whole spreadsheet' macro.

 Sheets("Import").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\My Folder\export.csv", Destination:=Worksheets("Import").Range("A1"))
        .Name = "exportnew"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Which, again, I got from the record macro wizard.

Thank you for writing out some code for me - I will try and tweak it to my spreadsheet and see if it works.

Regards,

Josh.

Hi Norie,

The code you provided worked once tweaked. Thank you very much for your help.

If you have the time, could you explain what was different about our approaches, so that I can avoid similar problems in the future?

Best wishes,

Josh.

Josh

When you open a text file in Excel you usually get a Text Import wizard.

This allows you to tell Excel the file is delimited or fixed width and you then go through various other steps.

On one of these steps (the 3rd) you can specify the data format for each column in the file.

One of the format options is Date, which itself has various date format options YMD, MDY, YDM etc

If you have a column with dates you should pick the Date option and pick the format that corresponds
to the data you are importing.

For example you had dates ormatted as DD.MM.YYYY so you would pick DMY.

After you've finished with the wizard you should hopefully have the data formatted as you want.

Since your data had already been imported I used Data>Text to columns... which has much the same
options as the Text Import Wizard.

Hope some of that makes some sort of sense.