Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

I can't getting error or information from website

0

hello

 I try  getting  information from  website   should  brings  table  contains  values  as  show  when  open  the  website. sorry  the  website  is  arabic  not  english  but  the  idea  just  brings  table  contains  values  . the  code  doesn't  brings  any thing  and  there  is  no  error .  I  hope  to find  some  help  here.

Sub GetSoccerStats()
    Dim xmlReq As New MSXML2.XMLHTTP60
    Dim objDoc As New MSHTML.HTMLDocument
    Dim objTable As MSHTML.htmlTable
    Dim objTableRow As MSHTML.htmlTableRow
    Dim strURL As String
    Dim strResp As String
    Dim strText As String
    Dim rw As Long
    Dim ws As Worksheet

    strURL = "https://www.saudiexchange.sa/wps/portal/tadawul/market-participants/issuers/issuers-directory/company-details/!ut/p/z1/04_Sj9CPykssy0xPLMnMz0vMAfIjo8zi_Tx8nD0MLIy83V1DjA0czVx8nYP8PI0MDAz0I4EKzBEKDEJDLYEKjJ0DA11MjQzcTfW99KPSc_KTIGZllJQUWKkaqBqUJKYklpfmqBroRyXn5xYk5lUGV-Ym5QMVGRkYG-iHEzK1IDsop6zSUREAbm86Ww!!/#chart_tab2/"
    With xmlReq
        .Open "GET", strURL, False
        .send

        strResp = .responseText
    End With

    'Worksheets.Add
    Set ws = sheet1
    With ws
    objDoc.body.innerHTML = strResp

    Set objTable = objDoc.getElementsByClassName("dataTables_processing")(0)


    If Not objTable Is Nothing Then
        rw = 1
        For Each objTableRow In objTable.Rows
            strText = objTableRow.Cells(0).innerText

                    Cells(rw, "a").Value = objTableRow.Cells(0).innerText

                    rw = rw + 1

        Next objTableRow
        Columns("a").AutoFit
    End If
    End With
    Set xmlReq = Nothing
    Set objDoc = Nothing
    Set objTable = Nothing
    Set objTableRow = Nothing


End Sub
Answer
Discuss

Answers

0

Hi Abdo

Your URL points to the SARCO page under the Saudi Exchange website (which I can see in English) but I think it is blocking the HTTP request "GET".

When I stepped through your code (with that URL) I saw that there was no response (since strResp became "").

I then commented out the line setting your URL, pointed to a new URL and added a message box line: 

    
    'strURL = "https://www.saudiexchange.sa/wps/portal/tadawul/market-participants/issuers/issuers-directory/company-details/!ut/p/z1/04_Sj9CPykssy0xPLMnMz0vMAfIjo8zi_Tx8nD0MLIy83V1DjA0czVx8nYP8PI0MDAz0I4EKzBEKDEJDLYEKjJ0DA11MjQzcTfW99KPSc_KTIGZllJQUWKkaqBqUJKYklpfmqBroRyXn5xYk5lUGV-Ym5QMVGRkYG-iHEzK1IDsop6zSUREAbm86Ww!!/#chart_tab2/"
    ' use another URL
    strURL = "https://superleaguetriathlon.com/event/londonuk21/"

    With xmlReq
        .Open "GET", strURL, False
        .send

        strResp = .responseText
    End With
    ' display response
    MsgBox strResp

Suggest you copy this into your code and try with both your URL and mine. It will show some text for my URL but nothing for yours.

You might need to find another method to get your info.

Hope this helps

Discuss

Discussion

Hi John,
actually  this  is the  first  time  to  deal  information from external  source  espicially  from  the  internet .
I think it is blocking the HTTP request "GET".
this  means  I  can't  get the  information? I tested  your  URL  it  shows  message  contains  lines  for  language HTML  but  my  URL gives  empty.
thanks  for  your  supporting
Abdo M (rep: 6) Sep 10, '22 at 9:10 am
Abdo, sorry but this is not my area of expertise.

In VBA you can see that this method works for other sites but gives no text response from the "GET" on your site. That means there is no text (which the later parts of the macro use to extract the specific elements you need).

I don't know if the website owners have used anti-scraping measures to prevent access. You need to do more research, sorry.
John_Ru (rep: 3992) Sep 10, '22 at 9:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login