Load image based on Cell SKU

[Zepan]

Hello,

First time poster.

I have been reading various posts in regards to running a macro that would insert product images in to a sheet based on a SKU that is in a different column. My sheet has an image in Cell A3 based on the SKU code that is inputted in to D3. All of my images are stored in F:\Images\ I will have about 500 SKU's that all need images and will be named the same as the SKU I enter in to Column D.

Can this be done via a VBA script?


I hope somebody can help.

Thanks,

Matt

[Zepan]

not sure if I was clear in my first post.

I have a spreasheet with 1 workbook. On Cell D3 of the each of the worksheet I have a reference no. Depending on the value in cell D3 I would like to create a macro to look for images in F:\Images and return the image matching the reference number.

For Eg if i have "27736" in D3 I would like the macro to look for "27736.jpg" in C:\Images and return an image in A3

Any ideas?

[Zepan]

I managed to solve my problem and would like to share my solution in effort to help others.

Sub InsertImage()
Dim Pic As Object
Dim picname As String
Dim PicPath As String
Dim CntStart As String
Dim RowTopPixel As Integer
Dim LoopCnt As Integer
Dim rowpixelcnt As Integer
Dim RowCnt As Integer
Dim HdrCnt As Integer
Dim PicWidth As Integer
Dim PicHeight As Integer
Dim HdrHeight As Integer
Dim PicMargin As Integer
'=======================================================
' <<<<<===== BEGINNING OF PARAMETERS =======>>>>>>
'
'Path to where all the images are.
'
PicPath = "G:\XXXXX\PROD IMAGES\"
'
' HEADERS
' -------
'
' Number of header rows (including titles)
HdrCnt = 3
' Required height of the header rows in pixels.
HdrHeight = 40
'
' DATA ROWS
' ---------
'
' Height of data rows(must be smaller than the image height).
RowHeight = 90
'
' IMAGES
' ------
'
' Width of the images
PicWidth = 80
' Height of the images
PicHeight = 80
' Margin from LHS of sheet to picture
PicMargin = 5
'
' <<<<<====== END OF PARAMETERS =======>>>>>>
'===================================================
' Begining of main code
' ---------------------
'
'Set Header row height
'
For n = 1 To HdrCnt
Rows(n).Select
Selection.RowHeight = HdrHeight
Next n
'
'Get row count
'
CntStart = "B" & (HdrCnt + 1)
RowCnt = ActiveSheet.Range(CntStart).End(xlDown).Row
'
'Set the starting point for images to start at.
'
rowpixelcnt = (HdrCnt * HdrHeight)
'
' Now loop through each row and get picture based
' on item code, resizing row height to suit.
'
For LoopCnt = (HdrCnt + 1) To RowCnt
Rows(LoopCnt).Select
Selection.RowHeight = RowHeight
imgname = Right("00000000" & ActiveSheet.Cells(LoopCnt, 2).Value, 8)
picname = PicPath & imgname & ".jpg"
RowTopPixel = Range("B" & LoopCnt).Top + 2
ActiveSheet.Shapes.AddPicture picname, True, True, PicMargin, RowTopPixel, PicWidth, PicHeight
On Error Resume Next
rowpixelcnt = (((LoopCnt - HdrCnt) * RowHeight) + (HdrCnt * HdrHeight))
Next LoopCnt
'
' Jump back to the top of the sheet
'
Cells(1, 1).Select
End Sub