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
| Sub Demo_replace_split()
Dim T$, N&, SP$(), colonnes, colsuppr, E$, CS%
colonnes = Array("reussiteVictoires", "idCheval", "courses", "nomCheval", "victoires", "reussitePlaces", "places") 'les entetes
colsuppr = Array("idCheval") 'les colonnes a suprimer
ActiveSheet.UsedRange.Clear
With CreateObject("MSXML2.XMLHttp")
.Open "GET", "http://www.geny.com/flux-donnees-fiche-entraineur?id_entraineur=1004963", False
.setRequestHeader "DNT", "1"
.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64)"
On Error Resume Next
.send
On Error GoTo 0
If .Status = 200 Then T = .responseText Else Beep: Exit Sub
End With
'on coupe le debut
T = Split(T, "[{")(1)
'
'on remplace les guillemets et et croche droite
T = Replace(Replace(T, Chr(34), ""), "},", "")
'
'on suprime la/les colonne(s) correspondante(s) l'array colonnes(colSuppr)
For CS = 0 To UBound(colsuppr)
CsuPPr = Split(T, colsuppr(CS) & ":")
For i = 0 To UBound(CsuPPr)
T = Replace(T, colsuppr(CS) & ":" & Split(CsuPPr(i), ",")(0) & ",", "")
Next
'
' on en profite en meme temps de supprimer les entetes colonnes correspondante a l'array (colSuppr)
E = Replace(Join(colonnes, ","), colsuppr(CS) & ",", "")
Next
'
' on enleve les autres intitulées de colonnes dans les données
For i = 0 To UBound(colonnes): T = Replace(T, colonnes(i) & ":", ""): Next
'
'on pose le tableau
SP = Split(E & "{" & T, "{")
With [A1].Resize(UBound(SP) + 1)
.Value = Application.Transpose(SP)
.TextToColumns Comma:=True
End With
End Sub |
