Load an image based a cell contents
Hello,
I have been reading various posts on the web in regards to running a macro that would insert product images in to a sheet based on a contents are in a SKU that is in a different column.
My sheet has an needs to have an image in Cell A3 based on the SKU code that is in cell D3 (my sheet has over 500 SKU's). All of my images are stored in C:\Images\ All of my images are named the same as the SKU that will be entered in to Column D.
Can this be done via a VBA script? If yes, can somebody please show me a basic script. I'm new to scripts so please understnad if I ask lots of questions.
Thanks,
Matt
Look here to see how to list all the files in a folder
Use this code so you get your D column updated: http://www.excelexperts.com/VBA-Tips-List-Files-In-A-Folder
Try this
Supposing the full path in D column (e.g.: "C:\Images\Picture1.jpg"), use the code attached.
If D column contains only file names (e.g.: "Picture1.jpg"), you can easily change it in adjacent column E using CONCATENATE:
="C:\IMAGES\" & D3 (you will get "C:\IMAGES\Picture1.jpg)
If this is the case, just change OFFSET in the code so it looks at E column, but not D:
pic = cl.Offset(0, 3) - change to: pic = cl.Offset(0, 4)
Code:
Sub InsertImage()
Dim pic As String ' File path of a picture
Dim cl As Range
Set Rng = Range("A3:A500") ' Defining input range
For Each cl In Rng
pic = cl.Offset(0, 3) ' Location of the picture file:
' Same row, third column from A, i.e. column D
Set myPicture = ActiveSheet.Pictures.Insert(pic) ' Inserting picture from address in D column
' into column A
With myPicture ' Setting picture properties
.ShapeRange.LockAspectRatio = msoTrue ' Keep aspect ratio
.Height = 50 ' Set your own size
.Top = Rows(cl.Row).Top
.Left = Columns(cl.Column).Left
End With
Next ' Looping to the 500th row
End Sub