Using VBA to insert an image within a cell

rick6823

New Member
Joined
Mar 5, 2004
Messages
11
o_O

Hello;

I'm currently trying to create a product image database within Excel with product images and pricing and description information. I've imported the product information into a spreadsheet.

I'm finding it challenging to contain a product image within a cell based on file path information contained in a separate cell. Anyone know how to do this?? An example of the record layout is

Product Product_Number Product_Desc Image Path Image
Widget W1234 Super Widget c:\images <.jpg image>

I'd sincerely appreciate any help if anyone knows how to get this done!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Does it have to be in an cell? Will a comment do?
 
Upvote 0
Hi Rick, welcome to the board! :)

Images cannot be entered into a cell per se but can look as though they are. Each sheet has whats called a drawer layer which is an invisible layer over the top of cells and this holds images on sheets such as a picture or drawing or a chart etc. These objects cannot go into a cell themselves.

You can either reduce the size of the image or increase the dimensions of the cell to make it look like the image is in a cell.

Does that help?
 
Upvote 0
Rick,

Here’s one way.

Setup:
In column E, put the names of your images.
In column D, put the path to the images in column E.
In column C:
=D4&"\"&E4

e.g.
a_PRIME Insert pictures in WS.xls
CDEF
3
4C:\mypictures\widget01.jpgC:\mypictureswidget01.jpgPicturehere
5
6
7
8
9
10
11C:\Mydocuments\Picture\razoos.gifC:\Mydocuments\Picturerazoos.gifPicturehere
12
13
14
15
16
17C:\MyDocuments\Images\Pictures\thingies.jpgC:\MyDocuments\Images\Picturesthingies.jpgPicturehere
18
19
Sheet3


The following macro from Dave Peterson (Microsoft MVP) looks at the reference in column C, and places the image in column F (see Notes below). Put the macro in a standard module.
Code:
Sub testme01()
' Dave Peterson
Dim myPict As Picture
Dim curWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myPictName As Variant

Set curWks = Sheets(1)   ' Change to suit

curWks.Pictures.Delete

With curWks
    Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
    If Trim(myCell.Value) = "" Then
        'do nothing
    ElseIf Dir(CStr(myCell.Value)) = "" Then
        'picture not there!
        MsgBox myCell.Value & " Doesn't exist!"
    Else
        With myCell.Offset(0, 3) '3 columns to the right of C (F)
            Set myPict = myCell.Parent.Pictures.Insert(myCell.Value)
            myPict.Top = .Top
            myPict.Width = .Width
            myPict.Height = .Height
            myPict.Left = .Left
            myPict.Placement = xlMoveAndSize
        End With
    End If
Next myCell

End Sub
Notes:
1. See this line:

With myCell.Offset(0, 3)

Means 3 columns to the right of C i.e. column F. Change to suit. This is where the images will be placed.

2. You will have to manually size the cells in column F to fit the image. See these lines:
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left

The above code will give the impression that the images fit within the cell (as Parry states, images do not actually sit inside a cell – with Windows, the images “float” in the Drawing level above the cell).

3. Columns C, D and E can be hidden if you wish.

Regards,

Mike
 
Upvote 0
Thanks, this works great putting the image in a cell. How can I modify this to put the image in a comment?
 
Upvote 0
Rick,

Here’s one way.

Setup:
In column E, put the names of your images.
In column D, put the path to the images in column E.
In column C:
=D4&"\"&E4

e.g.

a_PRIME Insert pictures in WS.xls
CDEF
3****
4C:\mypictures\widget01.jpgC:\mypictureswidget01.jpgPicture*here
5****
6****
7****
8****
9****
10****
11C:\My*documents\Picture\razoos.gifC:\My*documents\Picturerazoos.gifPicture*here
12****
13****
14****
15****
16****
17C:\My*Documents\Images\Pictures\thingies.jpgC:\My*Documents\Images\Picturesthingies.jpgPicture*here
18****
19****
Sheet3


The following macro from Dave Peterson (Microsoft MVP) looks at the reference in column C, and places the image in column F (see Notes below). Put the macro in a standard module.
Code:
Sub testme01()
' Dave Peterson
Dim myPict As Picture
Dim curWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myPictName As Variant

Set curWks = Sheets(1)   ' Change to suit

curWks.Pictures.Delete

With curWks
    Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
    If Trim(myCell.Value) = "" Then
        'do nothing
    ElseIf Dir(CStr(myCell.Value)) = "" Then
        'picture not there!
        MsgBox myCell.Value & " Doesn't exist!"
    Else
        With myCell.Offset(0, 3) '3 columns to the right of C (F)
            Set myPict = myCell.Parent.Pictures.Insert(myCell.Value)
            myPict.Top = .Top
            myPict.Width = .Width
            myPict.Height = .Height
            myPict.Left = .Left
            myPict.Placement = xlMoveAndSize
        End With
    End If
Next myCell

End Sub
Notes:
1. See this line:

With myCell.Offset(0, 3)

Means 3 columns to the right of C i.e. column F. Change to suit. This is where the images will be placed.

2. You will have to manually size the cells in column F to fit the image. See these lines:
myPict.Top = .Top
myPict.Width = .Width
myPict.Height = .Height
myPict.Left = .Left

The above code will give the impression that the images fit within the cell (as Parry states, images do not actually sit inside a cell – with Windows, the images “float” in the Drawing level above the cell).

3. Columns C, D and E can be hidden if you wish.

Regards,

Mike

I'll be using this sometime soon. ;)

Thanks Guys
 
Upvote 0
I'll be using this sometime soon. ;)

Thanks Guys


It work's perfect if the file have the same name but I have this problem, my list of products have the ID, 1,2,3 but some pictures have the name 1_new.jpg, 2.jpg, new_3.jpg, etc.

I used "*" in the path to find all the elements with that ID like "C:\*1*.jpg" and thanks to the line ElseIf Dir(CStr(myCell.Value)) = "" Then it find the file, the problem is with the line Set myPict = myCell.Parent.Pictures.Insert(myCell.Value) in that line the code didn't recognize the "*" on the path, any idea how to fix this?
 
Upvote 0
Hi Rick,


Your code works great but I encountered a significant drawback. If I send to someone the excel file, the pictures wont display as the path is lost. Is there any way to embed the pictures in the cells as you would by using the Insert/Pictures command in the ribbon?

Thank you in advance.

iulique
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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