Sub testEvent()
Dim s As String, e, arH, ndx As Integer, arS As Long, arr, fil As String
fil = Application.GetOpenFilename("All Files (*.CSV), *.CSV")
s = GetSource(fil)
ndx = 0
With CreateObject("scripting.dictionary")
For Each e In Split(Split(s, vbNewLine)(0), ";")
arH = Array("Loi", "S_an", "S_ani", "S_barcode", "S_barinv", "S_is", "S_lndate", "S_rec", "S_sg", "S_title", "S_ty", "S_up", "S_vo")
For i = 0 To UBound(arH)
If arH(i) = e Then
.Add ndx, e
End If
Next
ndx = ndx + 1
Next
arS = UBound(Split(s, vbNewLine)) + 1
ReDim arr(1 To arS, 1 To .Count)
rw = 1
For Each e In Split(s, vbNewLine)
cnt = 1
a = Split(e, ";")
For i = 0 To UBound(a)
If .exists(i) Then
If cnt = 3 Then
For Each rep In Array("(", ")", "R", "CPLS", "CPL", "CP", "C")
arr(rw, cnt) = Replace(a(i), rep, vbNullString)
Next
arr(rw, cnt) = Replace(a(i), ",", ".")
arr(1, cnt) = "Price"
ElseIf cnt = 7 Then
arr(rw, cnt) = Replace(Left(a(i), 10), ".", "/")
arr(1, cnt) = "Price"
Else
arr(rw, cnt) = a(i)
End If
cnt = cnt + 1
End If
Next
rw = rw + 1
Next
Range("a1").Resize(rw - 1, .Count) = arr
End With
Erase arr
Columns(5).Insert shift:=xlToRight
splitBarcode
''''add your other replacement codes here and file saving codes
End Sub
Sub splitBarcode()
Dim lr As Long, rng, rng2, itm
lr = Cells(Rows.Count, 4).End(xlUp).Row
rng = Range("d1:d" & lr)
ReDim rng2(1 To lr, 1 To 2)
For i = LBound(rng) To UBound(rng)
For Each itm In Split(rng(i, 1), ",")
If Left(itm, 3) = 361 Then
rng2(i, 1) = itm: rng2(1, 1) = "Barcode 1"
Else
rng2(i, 2) = itm: rng2(1, 2) = "Barcode 2"
End If
Next
Next
Range("d1:e" & lr).Value = rng2
Columns(4).NumberFormat = "0"
Erase rng: Erase rng2
End Sub
Function GetSource(url As String) As String
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", url
.Send
Do: DoEvents: Loop Until .Readystate = 4
GetSource = .responsetext
.abort
End With
End Function
NOTE: not yet complete like other replacement, but take into account the index of the column (as mentioned by jindon) it should be defined name.
Bookmarks