4

I have a table with a column of links (URLs) to images. I want to load the linked images into the next cell over.

For example:

A1 contains

http://example.com/image.jpg

I want to load the image in B1.

How can I do it?

1

2 Answers 2

10

You cannot load pictures "into" an Excel cell. Image objects are on a layer above the cells.

You can use a macro to loop through cells and place the image aligned to the cell in the next column. For example, select all image URLs and then run

For Each cel In Selection
    cel.Offset(0, 1).Select
    ActiveSheet.Pictures.Insert(cel.Value).Select
Next cel

The images will not be "in" the cells, though, but their top left corners will be aligned with the top left corners of the cell next to the URL.

3
  • 1
    Where do I enter this code?
    – Cocoa Dev
    Apr 18, 2013 at 22:19
  • 1
    right-click the sheet tab and select "view code". In the code window copy the rows between an opening "Sub MyMacro()" and a closing "End Sub" line.
    – teylyn
    Apr 19, 2013 at 2:59
  • + 1 Nicely Done :) Apr 20, 2013 at 0:09
5

Although an image cannot be loaded literally into a cell, it is possible to load an image into a cell comment using VBA. The following code from this site defines a function that can be used in the worksheet to insert an image into a comment.

  Option Explicit

  Function InsertCommentImage(title As String, absoluteFileName As String)
     Dim commentBox As Comment

   ' Clear any comments before attempting to add them.
     '.ActiveCell.ClearComments // incorrect
     ActiveCell.ClearComments  // works!

   ' Define the comment as a local variable and assign the file name from the
   ' cellAddress input parameter to the comment of a cell.
     Set commentBox = Application.ActiveCell.AddComment
     With commentBox
        .Text Text:=""
        With .Shape
           .Fill.UserPicture (absoluteFileName)
           .ScaleHeight 3, msoFalse, msoScaleFromTopLeft
           .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
        End With

      ' Set the visible to True when you always want the image displayed, and
      ' to False when you want it displayed only when you click on the cell.
      .Visible = False
     End With
     InsertCommentImage = title
  End Function

This InsertCommentImage function has two arguments: a title that will appear in the cell the function is entered into, and a reference to the image's location and name.

The image can from a local file...

enter image description here

...or a URL...

enter image description here

...or a reference to a file location or a web link.

enter image description here

To install the function into the workbook with your links, first select the Developer tab from the main Ribbon and then choose Visual Basic (the first button on the left of the Developer tab). The VBA integrated development environment (IDE) window will appear.

Then make sure that "VBAProject(your workbook name)" is highlighted in the "Project - VBA Project" pane (the left upper pane).

From the main VBA IDE menu, select Insert/Module and paste the function code in the large code pane that has opened up.

Close up the VBA IDE window and save the workbook. You can then use the function in the sheet with the links.

Not the answer you're looking for? Browse other questions tagged .