About getting data from table on website

muhittinemmi

New Member
Joined
Jun 20, 2023
Messages
19
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?

VBA Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi I solved the problem as below

VBA Code:
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?
 
Upvote 0
First, use the ServerXMLHTTP object instead of XMLHTTP. So replace...

VBA Code:
Set HTTP = CreateObject("MSXML2.XMLHTTP")

with

VBA Code:
Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")

Then use the setTimeouts method...

VBA Code:
HTTP.setTimeouts ...

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


Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top