VBA Insert image into a cell

[jamescooper]

The following code successfully downloads the image from the URL and saves it to the location specified.

Is there any way I could adapt it to insert it straight into the sheet?

Thanks in advance.

Sub SavingImagesfinal()


Dim http As New XMLHTTP60, htmldoc As New HTMLDocument
Dim htmlas As Object, htmla As Object, html As Object, item As Object
Dim stream As Object, tempArr As Variant, fileSource As String
Dim pic As String, myPicture As Picture, rng As Range
Dim cl As Range, AspectRatio As Double




With http
    .Open "GET", "https://www.tesco.com/groceries/en-GB/products/276054144/", False
    .send
    htmldoc.body.innerHTML = .responseText
End With


Set htmlas = htmldoc.getElementsByClassName("product-image__container")


For Each htmla In htmlas
    Set html = htmla.getElementsByTagName("img")(0)


    fileSource = Replace(html.src, "about", "http")
    tempArr = Split(html.src, "/")
    tempArr = tempArr(UBound(tempArr))


    With http
        .Open "GET", fileSource, False
        .send
    End With




        Set stream = CreateObject("ADODB.Stream")
        With stream
            .Open
            .Type = 1
            .write http.responseBody
            .SaveToFile ("C:\Users\jamesco\Desktop\CPPP\" & ".jpg")
            .Close
        End With
    Next htmla


End Sub

 

[jamescooper]

Any ideas please?

Thanks!

 

[Jon Peltier]

Reading your code, I don't know the URL of the image on the web. But it's been saved to your computer. Note; you don't need the parens around the file name, but you need a file name between the path and the extension:

     .SaveToFile "C:\Users\jamesco\Desktop\CPPP\" & "MyPicture" & ".jpg"

Then you can easily insert the picture into the worksheet. I have first selected Sheet1, then cell B3 on Sheet1, then inserted the picture.

    ActiveWorkbook.Worksheets("Sheet1").Select
    ActiveSheet.Range("B3").Select
    ActiveSheet.Pictures.Insert "C:\Users\jamesco\Desktop\CPPP\" & "MyPicture" & ".jpg"

Note that pictures (and all shapes) are not inserted "into" a cell, but they float in a drawing layer above the cells, and they can be aligned with cells. In this case, the top left corner of the picture covers the active cell when the picture is inserted.

 

[jamescooper]

Thanks that works, but can the .savetofile bit be modified to it saves it straight to the current workbook?

Thanks.

Set stream = CreateObject("ADODB.Stream")
With stream
.Open
.Type = 1
.write http.responseBody
.SaveToFile ("C:\Users\jamesco\Desktop\CPPP" & ".jpg")
.Close

 

[Jon Peltier]

A. I was going to say that you can always kill the image file after you insert it into Excel. Like what's the big deal.

B. Your original code finds the image and download it to your computer, then my code inserts it. I didn't read your code closely enough at first. But it looks like your variable filesource contains the http URL to your image. So you should be able to use this without actually saving the file:

ActiveSheet.Pictures.Insert filesource

 

[jamescooper]

Thanks Jon Peltier, much appreciated!