7

Can someone please explain to me how to turn a web link (URL) into an image.

Example image (URL is http://cache.lego.com/media/bricks/5/1/4667591.jpg)

http://cache.lego.com/media/bricks/5/1/4667591.jpg

What I am trying to do is make a parts list I have downloaded display the image rather than the above web link.

What I have in J2 to J1903 is:

http://cache.lego.com/media/bricks/5/1/4667591.jpg
http://cache.lego.com/media/bricks/5/1/4667521.jpg
...

What I would like to do is get excel to turn all these (10903 of them) into the pictures (cell size 81x81).

Can someone please explain step by step how I can do this?

  • In Office 2007 You can go to insert - Object - Create from file and paste the url in there. It will show the object with image icon in the excel worksheet however not with a thumbnail of the actual file. You can double click it to display. IF you need thumbnails i think you will have to download the files to local hdd and drag them to file manually. – mnmnc Jul 15 '15 at 11:26
  • Actually i found a way. Just go to insert - picture and in dialog box paste in a url as a file name. It simple as that. – mnmnc Jul 15 '15 at 11:29
  • Humm doesn't seem to like that? getting error "No connectivity with server. The file cache.lego.com/media/bricks/5/1/4121667.jpg cant be opened because the server couldn't be contacted. – Dave Gray Jul 15 '15 at 11:56
  • That is rather problem on your client/network side. I've tested it on URLs provided by you and it worked like a charm. What Office version are you using? – mnmnc Jul 15 '15 at 12:12
  • i am using 2010. Just wounder if there is a faster way of doing all 1903? going to take days. – Dave Gray Jul 15 '15 at 12:21
6

If you have a set of links in column J like:

enter image description here

and you run this short VBA macro:

Sub InstallPictures()
    Dim i As Long, v As String
    For i = 2 To 1903
        v = Cells(i, "J").Value
        If v = "" Then Exit Sub
        With ActiveSheet.Pictures
            .Insert (v)
        End With
    Next i
End Sub

each of the links will be opened and the associated Picture will be placed on the worksheet.

The pictures must the be properly sized and positioned.

EDIT#1:

Macros are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

enter image description here

To remove the macro:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the macro from Excel:

  1. ALT-F8
  2. Select the macro
  3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

EDIT#2:

To avoid stopping on retrieval errors, use this version:

Sub InstallPictures()
    Dim i As Long, v As String
    On Error Resume Next
        For i = 2 To 1903
            v = Cells(i, "J").Value
            If v = "" Then Exit Sub
            With ActiveSheet.Pictures
                .Insert (v)
            End With
        Next i
    On Error GoTo 0
End Sub
  • If this works, that's really nice answer. – mnmnc Jul 15 '15 at 12:29
  • I have not attacked the hard part ......all the pics must be positioned,etc. – Gary's Student Jul 15 '15 at 12:30
  • It's like writing a software for a client by the specifications provided - there is always a hidden required feature waiting around the corner waiting to be revealed. – mnmnc Jul 15 '15 at 12:34
  • Hi Thanks i have done ALT F11 copied and pasted the Sub InstallPictures() Dim i As Long, v As String For i = 2 To 1903 v = Cells(i, "J").Value If v = "" Then Exit Sub With ActiveSheet.Pictures .Insert (v) End With Next i End Sub but i am getting Compile error Invalid outside procedure? – Dave Gray Jul 15 '15 at 13:06
  • @DaveGray Perhaps it is an installation problem....see my EDIT#1 – Gary's Student Jul 15 '15 at 13:20
1

This one works much better in that the image end up beside the cell it belongs to.

Option Explicit
Dim rng As Range
Dim cell As Range
Dim Filename As String

Sub URLPictureInsert()
    Dim theShape As Shape
    Dim xRg As Range
    Dim xCol As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Set rng = ActiveSheet.Range("C1:C3000")   ' <---- ADJUST THIS
    For Each cell In rng
        Filename = cell
        If InStr(UCase(Filename), "JPG") > 0 Then   '<--- ONLY USES JPG'S
            ActiveSheet.Pictures.Insert(Filename).Select
            Set theShape = Selection.ShapeRange.Item(1)
            If theShape Is Nothing Then GoTo isnill
            xCol = cell.Column + 1
            Set xRg = Cells(cell.Row, xCol)
            With theShape
                .LockAspectRatio = msoFalse
                .Width = 100
                .Height = 100
                .Top = xRg.Top + (xRg.Height - .Height) / 2
                .Left = xRg.Left + (xRg.Width - .Width) / 2
            End With
isnill:
            Set theShape = Nothing
            Range("A2").Select
        End If
    Next
    Application.ScreenUpdating = True

    Debug.Print "Done " & Now

End Sub
1

This is my modification:

  • Replace cell with link with picture (not a new column)
  • Make pictures get saved with document (instead of links which can be fragile)
  • Make images slightly smaller so that they have a chance of sorting with their cells.

Code Below:

Option Explicit
Dim rng As Range
Dim cell As Range
Dim Filename As String

Sub URLPictureInsert()
    Dim theShape As Shape
    Dim xRg As Range
    Dim xCol As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    ' Set to the range of cells you want to change to pictures
    Set rng = ActiveSheet.Range("A2:A600")  
    For Each cell In rng
        Filename = cell
        ' Use Shapes instead so that we can force it to save with the document
        Set theShape = ActiveSheet.Shapes.AddPicture( _
            Filename:=Filename, linktofile:=msoFalse, _
            savewithdocument:=msoCTrue, _
            Left:=cell.Left, Top:=cell.Top, Width:=60, Height:=60)
        If theShape Is Nothing Then GoTo isnill
        With theShape
            .LockAspectRatio = msoTrue
            ' Shape position and sizes stuck to cell shape
            .Top = cell.Top + 1
            .Left = cell.Left + 1
            .Height = cell.Height - 2
            .Width = cell.Width - 2
            ' Move with the cell (and size, though that is likely buggy)
            .Placement = xlMoveAndSize
        End With
        ' Get rid of the 
        cell.ClearContents
isnill:
        Set theShape = Nothing
        Range("A2").Select

    Next
    Application.ScreenUpdating = True

    Debug.Print "Done " & Now

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.