How can I populate an Excel cell with an image?

Question

I'm trying to insert an image into an excel worksheet.

The code is simply:

Function AddImage(path As String, filename As String)
    Dim file As String
    file = path + "/" + filename + ".png"

    ActiveSheet.Range("A1").Pictures.insert(file).Select
End Function

but this doesn't work. When I set a watch on file I can see that it contains a valid path to an image on my hard drive.

What do I need to do to populate a cell with an image?

You can't insert an image in an excel cell. You may however place an image above the cell. This is such a common request. If you search Google, you will find plenty of examples — Siddharth Rout, Oct 18 '13 at 11:37
This may also help http://stackoverflow.com/questions/18197130/insert-text-into-the-background-of-a-cell/18199035 — , Oct 18 '13 at 12:25
I've tested ActiveSheet.Pictures.Insert file , no success. Excel 2007 Pro, Error 1004. — jacouh, Oct 18 '13 at 12:46
Adding a picture like that makes a reference to some file on your harddrive, for whatever reason if you want the image to be embedded in the file you have to add a shape and then put the image on the shape. user2140261's answer has the syntax for this — Dan, Oct 18 '13 at 13:43

user2140261 · Accepted Answer · 2013-10-18T14:31:12.997

3

You cannot put pictures "in" a cell, only "over" it. All pictures "float" on the worksheet. You can position a picture over a cell by setting its Top and Left properties to the Top and Left of the cell.

Sub AddPicOverCell(path As String, filename As String, rngRangeForPicture As Range)
With Application
Dim StartingScreenUpdateing As Boolean
Dim StartingEnabledEvent As Boolean
Dim StartingCalculations As XlCalculation

StartingScreenUpdateing = .ScreenUpdating
StartingEnabledEvent = .EnableEvents
StartingCalculations = .Calculation

    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Dim Top As Single, Left As Single, Height As Single, Width As Single
Dim file As String
Dim ws As Worksheet

file = path + "/" + filename + ".png"

Top = rngRangeForPicture.Top
Left = rngRangeForPicture.Left
Height = rngRangeForPicture.Height
Width = rngRangeForPicture.Width

Set ws = rngRangeForPicture.Worksheet

ws.Shapes.AddPicture file, msoCTrue, msoTrue, Left, Top, Width, Height

With Application
    .ScreenUpdating = StartingScreenUpdateing
    .EnableEvents = StartingEnabledEvent
    .Calculation = StartingCalculations
End With
End Sub

And then you would call it like:

AddPicOverCell "C:\", "Pic", ActiveSheet.Range("A1")

NOTES: This will position and resize the image to the same size and position on the sheet as the Cell you specify when calling the sub. This will insert the picture over the cell OR range you want the picture in. This could also be a range of cells like B5:G25 or as in my example a single cell like Range("A1") and the picture will cover all cells in the range.

edited Oct 18 '13 at 14:31

answered Oct 18 '13 at 13:38

user2140261

7,855
7
32
45

Some revision errors: ws is unset, rngCellForPicture unset, please concat path, filename + ".png" into filename to generalize the subroutine ? – jacouh Oct 18 '13 at 14:17
@jacouh What are you talking about? rngRangeForPicture is pased in doesn't need to be set. And the oringal code asked for the path, and filename to be passed that way in the question thats why I did it that way. but i did over look the ws, in original code it was set but after revisions I over looked. Code updated. – user2140261 Oct 18 '13 at 14:29
I'm finishing work now. I'll give this a try tonight. It looks good! – DaveDev Oct 18 '13 at 14:32
I see that you specified that I call it with `AddPicOverCell "C:\", "Pic", ActiveSheet.Range("A1")` but I'm not sure *where* to call it with that? – DaveDev Oct 18 '13 at 19:21
Simply awesome! Exactly what I needed! Congratulations, user2140261, for this great piece of code. One doubt... How do I add the picture without changing its "width"? The way it is, the picture is added, respecting the dimensions of the cell. That's cool and it's exactly what I need. The problem is that not all of my pictures are the same size, so many of them get "stretched" to fit the height and width of the cell. If I can set the height of the picture according to the height of the cell, without changing the width of the picture, it won't get stretched, it'll be perfect. Thanks a lot. – user48949 Jul 03 '14 at 14:54
@user48949 You would have load bring in the `FileSystemObject` and get the height of the original image, then get the ratio of the original height compared to the new height and apply that same ratio to the width. – user2140261 Jul 03 '14 at 22:10

score 1 · Answer 2 · answered Mar 27 '15 at 15:16

yes, you can add a picture to a cell, at least it works for me:

Sub testInsertAndDeletePicInCell()

Dim rng_PicCell         As Range
Dim thisPic             As Picture

Const MaxH = 50
Const MaxW = 14


    ' INSERT a picture into a cell

    ' assign cell to range
    Set rng_PicCell = ActiveSheet.Cells(2, 2) ' cell B2

    ' modify the range
    With rng_PicCell
        .RowHeight = MaxH
        .ColumnWidth = MaxW

        ' insert the picture
        Set thisPic = .Parent.Pictures.Insert("C:\tmp\mypic.jpg")

        ' format so the picture fits the cell frame
        thisPic.Top = .Top + 1
        thisPic.Left = .Left + 1
        thisPic.Width = .Width - 2
        thisPic.Height = .Height - 2

    End With


    Stop

    ' DELETE a picture
    thisPic.Parent.Pictures.Delete

End Sub

Gary's Student · Answer 3 · 2013-10-18T13:33:37.697

0

You need a Sub rather than a Function.

EDIT#1:

Make sure your path and filename are correct. Here is an example that works for me:

Sub qwerty()
    Dim p As Picture
    Dim sPath As String, sFileName As String, s As String
    sPath = "F:\Pics\Wallpapers\"
    sFileName = "mercury.jpg"
    s = sPath & sFileName
    Set p = ActiveSheet.Pictures.Insert(s)
End Sub

edited Oct 18 '13 at 13:33

answered Oct 18 '13 at 13:18

Gary's Student

95,722
10
59
99

How can I populate an Excel cell with an image?

3 Answers3

Linked