Results 1 to 5 of 5

Using VBA to insert an image within a cell

This is a discussion on Using VBA to insert an image within a cell within the Excel Questions forums, part of the Question Forums category; Hello; I'm currently trying to create a product image database within Excel with product images and pricing and description information. ...

  1. #1
    New Member
    Join Date
    Mar 2004
    Location
    San Diego, California, US
    Posts
    11

    Default Using VBA to insert an image within a cell



    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!

  2. #2
    Board Regular BrianM's Avatar
    Join Date
    Jan 2003
    Location
    New Bern, North Carolina
    Posts
    663

    Default Re: Using VBA to insert an image within a cell

    Does it have to be in an cell? Will a comment do?

  3. #3
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: Using VBA to insert an image within a cell

    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?

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Using VBA to insert an image within a cell

    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.

    ******** ******************** ************************************************************************>
    Microsoft Excel - a_PRIME Insert pictures in WS.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

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

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    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

  5. #5
    New Member
    Join Date
    Mar 2004
    Location
    San Diego, California, US
    Posts
    11

    Default Re: Using VBA to insert an image within a cell

    Thanks Tons!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com