Azərbaycan dili Bahasa Indonesia Bosanski Català Čeština Dansk Deutsch Eesti English Español Français Galego Hrvatski Italiano Latviešu Lietuvių Magyar Malti Mакедонски Nederlands Norsk Polski Português Português BR Românã Slovenčina Srpski Suomi Svenska Tiếng Việt Türkçe Ελληνικά Български Русский Українська Հայերեն ქართული ენა 中文
Subpage under development, new version coming soon!

Subject: get data from sokker using excel

  • 1
2020-06-25 17:59:19
dasanchez86 to All
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)
2020-06-25 18:00:48
for sure the address
should be
https://sokker.org/xmlinfo.php
2020-06-25 18:25:44
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)
2020-06-25 18:27:11
so this still works on online.sokker.org and http?
2020-06-25 18:50:33
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)
2020-06-25 19:42:12
so this still works on online.sokker.org and http?

Oh, yes! :)
2020-06-25 23:24:24
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
2020-06-26 18:33:56
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)
  • 1