Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Extract text from span element xml

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract text from span element xml

    Hi board!
    I want to get a text under span class = "black" from that website (its date and time of auction end):
    https://auction.violity.com/63039365...hkatulok-13sht
    Haven`t found any example except getting table content, but can`t handle even html-file opening
    Code:
    Sub Web_Test()
    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", "https://auction.violity.com/63039365-kollekciya-shkatulok-13sht", False
    '.Open "GET", "http://www.ernaehrung.de/lebensmittel/de/F110000/Apfel.php", False
    .send
    End With
    result = xml.responseText  'I have error here like: "selected encoding is not supported"
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = result
    ....
    
    end sub
    thanks in advance!

  2. #2
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract text from span element xml

    Try this;

    Code:
    Sub Test()
        Dim URL As String
        Dim IE As Object
        
        URL = "https://auction.violity.com/63039365-kollekciya-shkatulok-13sht"
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Navigate URL
        
        Do Until IE.ReadyState = 4
        Loop
        
        RetVal = IE.Document.getElementsByClassName("black")(3).InnerText
        
        MsgBox RetVal
        
        IE.Quit
        Set IE = Nothing
    End Sub

  3. #3
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text from span element xml

    Haluk, thanks for your reply, that worked!
    It uses internet explorer, so it is slow, do you know how take data without opening IE, using CreateObject("MSXML2.XMLHTTP.6.0") ? I just want to know how to open my link with that.

  4. #4
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract text from span element xml

    Hi again;

    Because of the encoding of the URL, i was not able to extract the data you want by HTTP request, at first.

    Now, this has been fixed by the below code.

    Code:
    Sub Test()
            Dim W As Object
            Dim URL As String, str1 As String, str2 As String, RetVal As String
            Dim x1 As Integer, x2 As Integer
            
            URL = "https://auction.violity.com/63039365-kollekciya-shkatulok-13sht"
            
            On Error Resume Next
                Set W = CreateObject("winhttp.winhttprequest.5")
                If Err.Number <> 0 Then
                    Set W = CreateObject("winhttp.winhttprequest.5.1")
                End If
            On Error GoTo 0
            
            On Error Resume Next
            W.Open "GET", URL, False
            W.send
            
            W.WaitForResponse
            
            If Err Then
                MsgBox "Server is not responding...", vbCritical
                Exit Sub
            End If
           
            If W.Status = 200 Then
                RetVal = StrConv(W.responseBody, vbUnicode)
                str1 = "
    "
                str2 = "+5"
                
                x1 = InStr(1, RetVal, str1)
                x2 = InStr(1, RetVal, str2)
                
                RetVal = Mid(RetVal, x1 + Len(str1), x2 - x1 - Len(str1))
                
                MsgBox RetVal
            End If
            
        Set W = Nothing
    End Sub
    
    Last edited by Haluk; Dec 9th, 2017 at 04:04 PM.

  5. #5
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract text from span element xml

    .... Deleted ....
    Last edited by Haluk; Dec 9th, 2017 at 04:37 PM.

  6. #6
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract text from span element xml

    Because of the CODE and HTML tags used for posting codes in this forum, the last code i have posted in message Num:4 is not reflecting the actual code.

    I will try once more again by not using the CODE tags....

    Edit: It didn't worked also.

    You can download the Excel file from the below link.

    https://drive.google.com/open?id=1IS...YXgAplJ3d4Pqeq
    Last edited by Haluk; Dec 9th, 2017 at 04:52 PM.

  7. #7
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    216
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract text from span element xml

    hi again, Haluk!
    thanks for help, I also found out that only problem was httpSecured; thanks for helping to access site data! now i can just parse it as i want!

  8. #8
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract text from span element xml

    Quote Originally Posted by S_Wish View Post
    ........I also found out that only problem was httpSecured;.....
    I didn't understand what you mean but; glad to see that your problem is solved.

    .

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •