Subpage under development, new version coming soon!
Subject: get data from sokker using excel
- 1
I am trying to download the data from http://online.sokker.org/xmlinfo.php to a worksheet in excel, but it seems I can't log in properly to sokker through the excel tool. do you know what could be the problem? can some guide me with it?
(edited)
(edited)
for sure the address
should be
https://sokker.org/xmlinfo.php
should be
https://sokker.org/xmlinfo.php
1. Show/Activate developer tools on excel
2. on developer tools Insert/Create active X button (developer modus should now been activated)
3. Button should be activated, click on Visual Basic
4. On Table/Sheet rightclick, show code
5. Insert e.g.:
Private Sub CommandButton1_Click()
Dim idPlayer, variable As Long
Dim i, pos1, pos2 As Integer
Dim sName As Name
With ActiveSheet.QueryTables.Add(Connection:="URL;http://online.sokker.org/start.php?session=xml", Destination:=Range("$K$1"))
.PostText = "ilogin=" & "YOURSOKKERLOGINNAME" & "&ipassword=" & "YOURSOKKERPASSWORD"
.Refresh BackgroundQuery:=False
End With
On Error Resume Next
ActiveWorkbook.Connections("Connection").Delete
For Each sName In ThisWorkbook.Names
sName.Delete
Next
i = 1
finish = False
Set XML = CreateObject("Microsoft.XMLHTTP")
While finish = False
i = i + 1
idPlayer = Cells(i, 1)
If idPlayer > 0 Then
web = "http://online.sokker.org/xml/player-" & idPlayer & ".xml"
XML.Open "POST", web, False
XML.Send
texto = XML.responseText
On Error Resume Next
'form
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 2) = variable
'value
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 3) = variable
'skillDiscipline
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 4) = variable
'skillExperience
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 5) = variable
'skillTeamwork
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 6) = variable
'matches
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 7) = variable
'injuryDays
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 8) = variable
'BMI
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 9) = variable
'age
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 10) = variable
'height
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 11) = variable
Else
finish = True
End If
Wend
Set XML = Nothing
End Sub
6. Deactivate developer modus
7. Insert on first raw (A2 till Axx) the player IDs
8. Click on button and you will get all demanded data
(If you need some more help, write what exactly data you want to have on your excel)
2. on developer tools Insert/Create active X button (developer modus should now been activated)
3. Button should be activated, click on Visual Basic
4. On Table/Sheet rightclick, show code
5. Insert e.g.:
Private Sub CommandButton1_Click()
Dim idPlayer, variable As Long
Dim i, pos1, pos2 As Integer
Dim sName As Name
With ActiveSheet.QueryTables.Add(Connection:="URL;http://online.sokker.org/start.php?session=xml", Destination:=Range("$K$1"))
.PostText = "ilogin=" & "YOURSOKKERLOGINNAME" & "&ipassword=" & "YOURSOKKERPASSWORD"
.Refresh BackgroundQuery:=False
End With
On Error Resume Next
ActiveWorkbook.Connections("Connection").Delete
For Each sName In ThisWorkbook.Names
sName.Delete
Next
i = 1
finish = False
Set XML = CreateObject("Microsoft.XMLHTTP")
While finish = False
i = i + 1
idPlayer = Cells(i, 1)
If idPlayer > 0 Then
web = "http://online.sokker.org/xml/player-" & idPlayer & ".xml"
XML.Open "POST", web, False
XML.Send
texto = XML.responseText
On Error Resume Next
'form
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 2) = variable
'value
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 3) = variable
'skillDiscipline
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 4) = variable
'skillExperience
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 5) = variable
'skillTeamwork
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 6) = variable
'matches
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 7) = variable
'injuryDays
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 8) = variable
'BMI
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 9) = variable
'age
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 10) = variable
'height
pos1 = InStr(texto, "") + Len("")
pos2 = InStr(texto, "")
variable = Mid(texto, pos1, pos2 - pos1)
Cells(i, 11) = variable
Else
finish = True
End If
Wend
Set XML = Nothing
End Sub
6. Deactivate developer modus
7. Insert on first raw (A2 till Axx) the player IDs
8. Click on button and you will get all demanded data
(If you need some more help, write what exactly data you want to have on your excel)
ok thank you both... I thought I could use the power query tool within excel in order to do it more convenient for me.
The procedure was something like this
https://youtu.be/aOd5sUf4yzM
but since you have to log in sokker, I used my user and password but it didn't work.
(edited)
The procedure was something like this
https://youtu.be/aOd5sUf4yzM
but since you have to log in sokker, I used my user and password but it didn't work.
(edited)
so this still works on online.sokker.org and http?
Oh, yes! :)
Oh, yes! :)
I have been reading about this and it seems to be a problem with the get & transform utility, when I used the legacy query it worked good.
Thank you
Thank you
update XD lol
I have been reading and I think I have to set this:
I used Allow as type, but I think I have to add something in order to define the POST variables... any ideas, help?
(edited)
I have been reading and I think I have to set this:
I used Allow as type, but I think I have to add something in order to define the POST variables... any ideas, help?
(edited)
- 1