Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA Image from HTML
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2014
    Posts
    629
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Image from HTML

    Using the following URL in cell S2: https://www.tesco.com/groceries/en-G...-fruit/bananas

    I am trying to pull all the image Src URL's on the page.

    Any ideas why this isn't quite correct please?

    Thanks.

    Code:
    Sub Images()
    
    
    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim LastRow As Long
    Dim Images As MSHTML.HTMLImgCollection
    Dim Image As MSHTML.HTMLImg
    Dim arrItems_1 As Variant
    Dim StrURL As String
    Dim rngURL As Range
    
    
    'Create URL and sent request
    For Each rngURL In Worksheets("Sheet1").Range("S2", Worksheets("Sheet1").Range("S" & Rows.Count).End(xlUp))
        XMLPage.Open "GET", rngURL, False
        XMLPage.send
        DoEvents
        
        'Get the source (code) of the webpage
        HTMLDoc.body.innerHTML = XMLPage.responseText
        
        LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
        
        Set Images = HTMLDoc.getElementsByClassName("product-list-container")(0).getElementsByTagName("img")
        
        For Each Image In Images
        If Image.className = "product-image" Then
        StrURL = StrURL & "|" & "https://img.tesco.com/" & Image.src
            End If
        
    Next
    
    
    Next
        'Store all results in an Array
    
    
        arrItems_1 = Split(Mid(StrURL, 2), "|")
        
        'Insert the results directly into Sheet1
    
    
        Sheets("Sheet1").Cells(LastRow, 1).Resize(UBound(arrItems_1) + 1) = Application.Transpose(arrItems_1)
        
    End Sub

  2. #2
    Board Regular
    Join Date
    Sep 2014
    Posts
    629
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    Any ideas please?

  3. #3
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,642
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Image from HTML

    Because they aren't in HTML when the page is loaded, they're referenced in JavaScript, but since your method doesn't execute the JavaScript, the images are never inserted.

  4. #4
    Board Regular
    Join Date
    Sep 2014
    Posts
    629
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    I would just like the image src URL not the actual image?

    Any ideas thanks.

  5. #5
    Board Regular
    Join Date
    Oct 2013
    Posts
    312
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    First, change these lines in the declaration section:
    Code:
    Dim Images As MSHTML.HTMLImgCollection
    Dim Image As MSHTML.HTMLImg
    To:
    Code:
    Dim Images As MSHTML.IHTMLElementCollection
    Dim Image As MSHTML.IHTMLElement
    Second, insert an if statement to check if rngUrl is empty:
    Code:
    If rngURL.Text <> "" Then
            XMLPage.Open "GET", rngURL, False
            XMLPage.send
            DoEvents
            
            'Get the source (code) of the webpage
            HTMLDoc.body.innerHTML = XMLPage.responseText
            
            lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
            
            Set Images = HTMLDoc.getElementsByClassName("product-list-container")(0).getElementsByTagName("img")
            
            For Each Image In Images
                If Image.className = "product-image" Then
                    StrURL = StrURL & "|" & "https://img.tesco.com/" & Image.src
                End If
            Next
        End If
    Using: Windows 10 + Microsoft Excel Professional 2016 (English version ! ! !)
    and also an Acer Chromebook with Google spreadsheet (Online).

  6. #6
    Board Regular
    Join Date
    Sep 2014
    Posts
    629
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    That produces the following URLs:

    https://img.tesco.com/data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==
    https://img.tesco.com/data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==
    https://img.tesco.com/data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==
    https://img.tesco.com/data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==
    https://img.tesco.com/data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==
    https://img.tesco.com/data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==

    Which aren't actually correct. I managed to get this before.

    What I am looking for is, for example:

    https://img.tesco.com/Groceries/pi/0...ot_225x225.jpg

  7. #7
    Board Regular
    Join Date
    Oct 2013
    Posts
    312
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    I noticed that also. But my first attempt was to get rid of the error messages I got.

    I will have a look at it later. I'm working at the moment.

  8. #8
    Board Regular
    Join Date
    Sep 2014
    Posts
    629
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    Thanks.

  9. #9
    Board Regular
    Join Date
    Oct 2013
    Posts
    312
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    I looked into it and agree with Kyle123.

    Quote Originally Posted by Kyle123 View Post
    Because they aren't in HTML when the page is loaded, they're referenced in JavaScript, but since your method doesn't execute the JavaScript, the images are never inserted.
    So you get something like: "data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==

    This is a technique I'm not familiar with. It converts images to base64 data-URIs for embedding as image URLs in the HTML-code. Sorry I cannot help you any further.
    Using: Windows 10 + Microsoft Excel Professional 2016 (English version ! ! !)
    and also an Acer Chromebook with Google spreadsheet (Online).

  10. #10
    Board Regular
    Join Date
    Sep 2014
    Posts
    629
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Image from HTML

    Thanks for the feedback. Kyle123 do you know how this would be developed?

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
  •