Display images from URL's

[micmed]

I have searched for this but have not seen a situation just like mine.

I have URL's in a sheet that point to images on Photobucket. Is there a simple way to have the images display in a cell adjacent to the cell with the URL?

This is a sheet with 1000's of parts items. Each item may have 1 to 10 images each.

 

[ukracer]

Interesting I have exactly the same issue and I am still to find a solution on here or anywhere else . I dont suppose you or anyone else has found the answer in the last 3 years???

 

[John_w]

Try running this code. It assumes the image URLs are in column A starting at A2 and it puts each image in the adjacent B cell.

Public Sub Add_Images_To_Cells()

    Dim lastRow As Long
    Dim URLs As Range, URL As Range
    
    With ActiveSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
        Set URLs = .Range("A2:A" & lastRow)
    End With

    For Each URL In URLs
        URL.Offset(0, 1).Select
        URL.Parent.Pictures.Insert URL.Value
        DoEvents
    Next
    
End Sub

 

[ukracer]

HI mate thanks for your reply. Its seems and easy thing to do but some 5 hours later I still dont have a solution and have read most web sites in the world (or so it seems)

Just 2 questions is that a macro and I have excel 2010 is it the same. IT seems like 2013 will let me do what I need easily but I can not afford to buy that as I only just purchased 2010 lol

 

[ukracer]

Thanks for that I managed to get a test run and it did indeed grab the images I need . sadly it did not place them as thumb nails or place them in a cell. (or so it seems ) I am much further forward though now.

Does anyone know how to modify the code to place them as thumbnails??

Thanks a lot for this though, so far its very helpful indeed.

 

[John_w]

With the macro recorder running, resize one of the images and apply the code generated to the previously posted code. If you're unable to apply the code, search for the Excel objects in the code along with other related words.

 

[ukracer]

MMm I captured the code but it throws an error when running it in the original macro. I cant see where the macro decide on the size it will import as either

 

[John_w]

The code imports the images full size and then they can be scaled or sized to fit. Try the following code which inserts and resizes each image to the size of the cell in which it sits:

Public Sub Add_Images_To_Cells()

    Dim lastRow As Long
    Dim URLs As Range, URL As Range
    Dim pic As Picture
    
    With ActiveSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).row
        Set URLs = .Range("A2:A" & lastRow)
    End With

    For Each URL In URLs
        URL.Offset(0, 1).Select
        Set pic = URL.Parent.Pictures.Insert(URL.Value)
        With pic.ShapeRange
            .LockAspectRatio = msoFalse
            .Height = URL.Offset(0, 1).Height - 1
            .Width = URL.Offset(0, 1).Width - 1
            .LockAspectRatio = msoTrue
        End With
        
        DoEvents
    Next
    
End Sub

Play around with different .Height and .Width values if you want a different size.

If you want to scale the images instead then replace the code inside the With ... End With block with:

            .ScaleWidth 0.3, msoFalse, msoScaleFromTopLeft
            .ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft

again, play around with different scales as necessary.

 

[ukracer]

Thanks John I think that will get me out of a mess lol

 

[ukracer]

Sweet absolutely sweet. It works a treat. Just out of interest what does scaling it do. I am assuming it is like 30% of the full size etc (depending on scale used??

But you are a star. Many thanks.

Regards Andy T