About getting data from table on website

[muhittinemmi]

Hello
I want to pull the data from the table in the specified site with the sample code below, but it only takes the 1st row of the table.

How should I change to get the whole table?

Sub QueryGoogleSheets()
      Dim qt As QueryTable, url As String, key As String, gid As String
 
      If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
      ActiveSheet.Cells.Clear
 
      url = "https://www.qnbfinansbank.com/kur-bilgileri"
 
      
      Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & url, _
      Destination:=Range("a2"))
    
 
      With qt
          .WebSelectionType = xlAllTables
          .WebFormatting = xlWebFormattingNone
          .Refresh
      End With
  End Sub

 

[muhittinemmi]

Hi I solved the problem as below

Sub Dovizal()


 
    Dim HTTP As Object, HTML As Object, i As Integer, j As Integer
    Dim myURL As String
    
    Range("S6:o" & Rows.Count) = ""
    Range("T6:U6") = Array("ALIŞ", "SATIŞ")
    
    Set HTML = CreateObject("HTMLFILE")
    Set HTTP = CreateObject("MSXML2.XMLHTTP")
    
    myURL = "https://www.qnbfinansbank.enpara.com/hesaplar/doviz-ve-altin-kurlari"
    
    HTTP.Open "GET", myURL, False
    HTTP.send
    
    HTML.body.innerHTML = HTTP.responseText
    Set objCollection = HTML.getElementsByTagName("div")
    
    j = 6
    
    For i = 0 To objCollection.Length - 1
        If objCollection(i).ClassName = "enpara-gold-exchange-rates__table-item USD" Or _
            objCollection(i).ClassName = "enpara-gold-exchange-rates__table-item EUR" Or _
            objCollection(i).ClassName = "enpara-gold-exchange-rates__table-item XAU" Then
            
            j = j + 1
            
            Range("S" & j) = objCollection(i).getElementsByTagName("span")(0).innerText
            Range("T" & j) = Replace(objCollection(i).getElementsByTagName("span")(1).innerText, " TL", "") + 0
            Range("U" & j) = Replace(objCollection(i).getElementsByTagName("span")(2).innerText, " TL", "") + 0
        End If
    Next
    
    Set HTML = Nothing
    Set HTTP = Nothing

          Sheets("Teklif").Select
          Range("t4  ") = Format(Now, "dd.mm.yyyy hh:mm")
          
  End Sub

How can I get a warning that data could not be updated if there is no response within 10 seconds or there is no internet?

 

[Domenic]

First, use the ServerXMLHTTP object instead of XMLHTTP. So replace...

Set HTTP = CreateObject("MSXML2.XMLHTTP")

with

Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")

Then use the setTimeouts method...

HTTP.setTimeouts ...

Have a look at the following article for a guide on how to set the parameters for setTimeouts...

setTimeouts Method

learn.microsoft.com

Hope this helps!