Insert Product Images to Excel Workbook

[RockyRobin]

Hi All

Complete Newbie here and am trying to enhance my knowledge of excel to stop myself taking hours to do jobs.

I have a stocklist that is currently in excel with product codes in column B. (eg 60080U-090)
I want to insert product images into column A. The product images are labelled as the style code (eg 60080U-090)

Now this is where you will chuckle, previously we have spent hours manually adding each image into the relevant cell and resizing.

I dont know much about macros/VBA but am more than willing to start learning.

If its any help:

Images are saved on a folder in the desktop labelled "Imagery"
The excel workbook is saved in the same folder and labelled "Stock"

Hope you guys can help me out and enlighten me.

Thanks in advance.

 

[RockyRobin]

Try:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Dim Pic As Picture
    Application.ScreenUpdating = False
    Set Rng = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    For Each Cell In Rng
        With Cell
            On Error Resume Next
            Set Pic = .Parent.Pictures.Insert(ThisWorkbook.Path & Application.PathSeparator & .Value & ".jpg")
            If Err = 0 Then
                With .Offset(, -1)
                    Pic.Top = .Top
                    Pic.Left = .Left
                    Pic.Height = .Height
                    Pic.Width = .Width
                End With
            Else
                Err.Clear
            End If
            On Error GoTo 0
        End With
    Next Cell
    Application.ScreenUpdating = True
End Sub

Andrew you are a LEGEND
That has worked perfectly.

Can I ask a quick question.

If i add new images to the folder (to cover any missing ones) at a later date, will it automatically update on the spreadsheet?
Or is there a process I need to follow.

Cannot thank you enough.
This will save hours going forward.

 

[Andrew Poulsom]

If i add new images to the folder (to cover any missing ones) at a later date, will it automatically update on the spreadsheet?
Or is there a process I need to follow.

No, new images won't be inserted automatically. You can add the names of new images below the last used cell in column B. If the additional names start in row 734 change B2 to B734 in the macro and rerun it.

 

[RockyRobin]

If the images I add are for a style that is on ROW 20 for example, that was blank after i ran the macro the first time, will I need to run the macro again to allow it to be recognised and populate the cell?

Also if I wanted to set the image size is this something I set in the macro. Or what is the best way to format the sheet once the macro has been run.
I've tried to format the cell to align them to centre but it hasnt worked.
(I'm probably just being picky now but would like to get a complete grasp of it as this is a job I will have to do often)

 

[Andrew Poulsom]

You have to run the macro again (with the Rng assignment suitable amended) if you want to add more images. Nothing is automatic.

The image's size is set in the macro here:

Pic.Height = .Height
Pic.Width = .Width

Currently it's the size of the cell, but you can change that.

 

[RockyRobin]

You have to run the macro again (with the Rng assignment suitable amended) if you want to add more images. Nothing is automatic.

The image's size is set in the macro here:

Pic.Height = .Height
Pic.Width = .Width

Currently it's the size of the cell, but you can change that.

Thanks Andrew.
Final Question I promise.
If i need to save the file and pass onto someone else without giving them the image folder, how do i "embed" the images in that sheet. Is that possible,

 

[Andrew Poulsom]

The inserted images are embedded in the workbook.

 

[RockyRobin]

The file size is very small.
When I've save it, put it on a memory stick and then open it on a different pc, the images arent visible.

 

[Andrew Poulsom]

I can't reproduce that behaviour. How are you saving the workbook?

 

[RockyRobin]

Tried a couple of ways.

File / Save As / Excel Workbook (608KB)
File / Save As / MAcro Enabled Workbook (268kb)

 

[RockyRobin]

Probably a stupid question. Do I need to save the VBA file too in order for the images to embed.