VBA Insert image into a cell

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
834
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.

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

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:

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

Code:
    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.
 
Upvote 0
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
 
Upvote 0
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:

Code:
ActiveSheet.Pictures.Insert filesource
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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