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
| Sub Extraire()
Call ExtractionData("http://aguila-bidart.fr/demo/ISSY/webservice.php?ndDay=31")
End Sub
Sub ExtractionData(URL As String)
Dim S As Worksheet
Dim R As Range
Dim R2 As Range
Dim IE As Object
Dim A$
Dim var
Dim i&
'---
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate (URL)
'---
Do While IE.Busy
DoEvents
Loop
'---
A$ = IE.document.body.InnerText
'---
IE.Quit
Set IE = Nothing
'###########################
If A$ = "" Then Exit Sub
A$ = Replace(A$, "[", ",")
A$ = Replace(A$, "]", "")
A$ = Replace(A$, ",{" & Chr(34) & "value_Mesure" & Chr(34) & ":" & Chr(34), "")
A$ = Replace(A$, "," & Chr(34) & "timestamp_Mesure" & Chr(34) & ":" & Chr(34), "")
var = (Split(A$, "}"))
Set S = Sheets.Add
'---
For i& = LBound(var) To UBound(var)
S.Range("a" & i& + 1 & "") = var(i&)
Next i&
'---
S.[a1].CurrentRegion.TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=""""
'--- Format "dd/mm/yyyy hh:mm:ss" ---
For i& = 1 To UBound(var)
Set R = S.Range("b" & i& & "")
Set R2 = R.Offset(0, 1)
R2 = (R / 86400) + CDbl(CDate("1/1/1970") + (1 / 24))
R2.NumberFormat = "dd/mm/yyyy hh:mm:ss"
Next i&
End Sub |
Partager