VBA Split cells date format

[MichielVanGend]

Hi,

I want to splits cells (text to columns) with a VBA Macro.
I'm using the following code:


Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False

This works but with one slight problem:

9999;1-2-2019;test (<- this date is januari second)

gets split as:

9999|2-1-2019|test

If I perform the same action manually it gets splits as:

9999|1-2-2019|test

The last option is the one that I need.
So manually it uses European dates, with VBA It uses American dates.

Normally I don’t know how many date fields will be in my data or on what position they occur.

Is there a way to get the dates in the right format?

 

[bobsan42]

yes there is.
Check here: https://docs.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns
Use the FieldInfo parameter to specify data type by columns.