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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
| Sub Treatment1(ColumnsToDelete, SourceName As String, Optional TargetName As String)
Dim s As ADODB.Stream
Dim Source As String
Dim t, r
Dim i As Long, j As Long, cols As Long
Dim Text As String, Target As String
If TargetName = "" Then TargetName = SourceName
Set s = New ADODB.Stream
s.Charset = "utf-8"
s.Open
s.LoadFromFile SourceName
Source = s.ReadText
s.Close
t = Split(Source, vbCrLf)
For i = 0 To UBound(t)
r = Split(t(i), ",")
If UBound(r) <> -1 Then
If cols = 0 Then cols = UBound(r)
Range("a" & i + 1).Resize(1, UBound(r) + 1).Value = r
End If
Next i
For i = UBound(ColumnsToDelete) To 0 Step -1
Cells(1, ColumnsToDelete(i)).EntireColumn.Delete
Next i
t = Cells(1, 1).Resize(UBound(t) + 1, cols - UBound(ColumnsToDelete)).Value
For i = 1 To UBound(t)
Text = ""
For j = 1 To UBound(t, 2)
Text = Text & t(i, j) & ","
Next j
Text = Left(Text, Len(Text) - 1)
Target = Target & Text & vbCrLf
Next i
Set s = New ADODB.Stream
s.Charset = "utf-8"
s.Open
s.WriteText Target
s.SaveToFile TargetName, adSaveCreateOverWrite
End Sub
Sub Test1()
Dim Debut As Date, Fin As Date
Debut = Now
Treatment1 VBA.Array(2, 4), "c:\data\temp\source.csv", "c:\data\temp\target.csv"
Fin = Now
Debug.Print Format(Fin - Debut, "hh:mm:ss")
End Sub |
Partager