Log In
Register
Home
Active Member
April 8, 2021
Offline
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
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
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
Answers Post
1 Guest(s)
