Getting data from web

[Jagulari]

Hi!

I have a problem, involving getting data from web into excel.
I want to take data from site euw.op.gg/summoner/userName=Calsot , where in the URL the last word, (for example Calsot) is the username of a player in a MOBA game. The data I'd like to get is his ranking. I know its written in the pages source code on row 336.
My goal is to make an excel worksheet, where i.e. into column A i would write the user name (Calsot, niQ, Airwax etc.) and into column B their rankings would appear.
I tried to make a VBA code to change the end of the URLs to the value in column A and make each unique URL look for source code row 336 and their ranking value.
As you could guess, I didnt succeed and I couldnt modyfy codes I found on internet to fullfill my needs.

Sadly I have no code to show you, because they didnt work and I continually deleted every failed effort I made. :P

[pike]

Hi Jagulari
This will get you started

Option Explicit
Sub Web_Table_Option_One()
Dim xml As Object
Dim html As Object
Dim objTable As Object
Dim result As String
Dim lRow As Long
Dim lngTable As Long
Dim lngRow As Long
Dim lngCol As Long
Dim ActRw As Long
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", "http://euw.op.gg/summoner/userName=Calsot", False
.send
End With
result = xml.responseText
Set html = CreateObject("htmlfile")
html.body.innerHTML = result
Set objTable = html.getElementsByTagName("table")
For lngTable = 0 To objTable.Length - 1
For lngRow = 0 To objTable(lngTable).Rows.Length - 1
For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
ThisWorkbook.Sheets("Sheet2").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
Next lngCol
Next lngRow
ActRw = ActRw + objTable(lngTable).Rows.Length + 1
Next lngTable

Set xml = Nothing
Set html = Nothing
Set objTable = Nothing
End Sub


there will be a tag in the source code which will contains the value

[snb]

Also in this case you can use a webquery.

[p45cal]

or a user defined function:11655