1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| Sub Test()
Dim Fichier As String
Dim Wbk As Workbook
Dim N As Long
Dim Tb
Application.ScreenUpdating = False
Fichier = "C:\Users\VAL\Desktop\Test2.csv"
Set Wbk = Workbooks.Open(Filename:=Fichier, Local:=True)
With Wbk
With .Worksheets(1)
N = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1").Resize(N).TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, Semicolon:=True, Other:=False
.Range("E1").Resize(N) = FORMATER(.Range("A1").Resize(N).Value)
.Range("A1").Resize(N, 5).Sort key1:=.Range("E1"), Order1:=xlDescending, Header:=xlNo
.Range("E1").Resize(N).ClearContents
.Range("A1").Resize(N, 4).RemoveDuplicates Columns:=Array(2, 3), Header:=xlNo
End With
.Close True
End With
Set Wbk = Nothing
End Sub
Function FORMATER(ByRef Tb)
Dim i As Long
Dim Tmp
For i = 1 To UBound(Tb, 1)
Tb(i, 1) = Replace(Tb(i, 1), "_", "-")
Tb(i, 1) = Replace(Tb(i, 1), Chr(150), "-")
Tmp = Split(Tb(i, 1), "-")
Tb(i, 1) = Tmp(2) & "-" & Tmp(1) & "-" & Tmp(0) & "-" & Tmp(3) & "-" & Tmp(4) & "-" & Tmp(5)
Next i
FORMATER = Tb
End Function |
Partager