Hello All,
I have a ".CSV" File (Exportación de datos - Movimiento facturas de venta - 2021-05-15T180919.705), which is dowloaded from a web ERP. A second File ".xlsm" ("Movimiento_venta_2020_V4.xlsm") is used to run a couple of macros (scrap data, consolidating, extracting......). However, one of these macros opens the ".CSV" File, split the data (TextToColumns), with the aim to copy it on the ".xlsm" File. The ISSUE is caused once the code "TextToColumns " is used to split the data, changing the format of SOME of the cells from "dd/mm/yy" to "mm/dd/yyyy". See enclosed the picture "TextoColumn_Before-After.JPG".
I have used several parameters of the TextToColumns functions but I have not succeeded.
Note: in the case is relevant -> The ERP use Spanish Language and my Excel is in English Language
------------------------------------------
Location of the Macro:
File: "Movimiento_venta_2020_V4.xlsm" (size is to big to attached)
Module: Web
Sub: OpenLatestFile()
code currently used: Range("A:A").TextToColumns , DataType:=xlDelimited, Semicolon:=True
Other Code attempts:
'Range("A:A").TextToColumns , DataType:=xlDelimited, Semicolon:=True, TextQualifier:=xlDoubleQuote, _
' FieldInfo _
' :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, xlDMYFormat), _
' Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
' ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
' (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
' Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
' 33, 1), Array(34, 1), Array(35, 1)), TrailingMinusNumbers:=True
-----------------------------------------------
The ".CSV" File (Exportación de datos - Movimiento facturas de venta - 2021-05-15T180919.705) enclosed was cutted in size to be able to attached it here.
---------------------------------------------------
Thanks in advance
Why not using Power Query to import csv data?
It's much more powerful than TextToColumns.
To make the current code work, you have to change your regional settings from US date format to a region that uses dd/mm/yyyy date format, not US date format mm/dd/yyyy, you will no longer have that problem.
Currently, your computer date format does not match the csv date format.
Just for information (since Power Query is a better option if you have it), the problem is that you specified the date format for the wrong columns - you did it for column 6, but you actually need it for 7, 8 and 9:
Range("A:A").TextToColumns , DataType:=xlDelimited, Semicolon:=True, TextQualifier:=xlDoubleQuote, _
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, xlDMYFormat), Array(8, xlDMYFormat), Array(9, xlDMYFormat), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1)), TrailingMinusNumbers:=True
Dear Velouria and Catalin,
Thanks for the reply, was really helpful. I have switched to Power Query.
