Product Image link to product code

[Ronron1]

Hey Guys,

This is my first post on your wonderful forum.

I have been looked through this forum but cant seem to find what I am looking for.

I have a file on my hard-drive with all my product images, I would like to create a Marco to assign the product image next to the product code.

The first cell value is B25 which contains the first product code as a text, I would like to display all the pictures starting from A25 downwards.

I did search the forum for and found this thread

http://www.mrexcel.com/forum/excel-questions/712251-macro-load-picture.html

and tried to modified the code for my purpose but it did not work for me.

Option Explicit
Sub Insert_Pic()
Dim filename As String
Dim picpath As String
picpath = "F:\PHOTO\ALL PHOTO\" ' C:\Users\SJR\Desktop\Product Images
Range("a25").Select
filename = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
On Error Resume Next
ActiveSheet.Pictures.Insert(picpath + filename + ".jpg").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 100
Selection.ShapeRange.Width = 134
Selection.Cut
ActiveSheet.Paste
ActiveCell.RowHeight = 100
ActiveCell.ColumnWidth = 25
End Sub

Any help would be much appreciated.

Thanks
Ron

 

[sijpie]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Insert_Pic()<br>    <SPAN style="color:#00007F">Dim</SPAN> sFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sPicPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sExt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rProd <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <br>    sPicPath = "F:\PHOTO\ALL PHOTO\" <SPAN style="color:#007F00">' C:\Users\SJR\Desktop\Product Images</SPAN><br>    sExt = ".jpg"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rProd = Range("A25")    <SPAN style="color:#007F00">' column A contains image name</SPAN><br>    <SPAN style="color:#007F00">'Start inserting pictures while sProd  isn't empty</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> rProd.Value <> vbNullString<br>        sFileName = rProd.Value<br>        <SPAN style="color:#007F00">' put images in column C, as B contains product code</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> rProd.Offset(0, 2)<br>            .RowHeight = 100<br>            .ColumnWidth = 25<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#007F00">' by using the following construction we are _<br>          working with the new image</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Pictures.Insert(sPicPath + sFileName + sExt)<br>            <SPAN style="color:#00007F">With</SPAN> .ShapeRange<br>                .LockAspectRatio = msoTrue<br>                .Height = 100<br>                <SPAN style="color:#00007F">If</SPAN> .Width > 134 <SPAN style="color:#00007F">Then</SPAN> .Width = 134<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#007F00">' set the position relative to the cell</SPAN><br>            .Left = rProd.Offset(0, 2).Left<br>            .Top = rProd.Offset(0, 2).Top<br>            .Placement = 1<br>            .PrintObject = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#00007F">Set</SPAN> rProd = rProd.Offset(1, 0) <SPAN style="color:#007F00">'move one cell down</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <SPAN style="color:#007F00">'clean up</SPAN><br>   <SPAN style="color:#00007F">Set</SPAN> rProd = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Notice that the code does not contain any '.select' which is slow and unnecessary. By using a range object (rProd), we can do all actions with the range object as reference.

Check to see if the columns are correct. I have assumed image name in column A, product code in column B and image in column C

 

[Ronron1]

Hi,

Thank you so much for your reply.

I have tried running your modified marco but have run into an error...

I have image name in column A1 to A1099 that is my product code with .jpg after it.

In column B I have put the product name without any extension.

Is there a step I am missing??

Thanks in advance

 

[sijpie]

Can you copy paste a few rows of column A & B into this forum? If you put some borders around the cells before copying it will come out quite reasonable

 

[Ronron1]

Can you copy paste a few rows of column A & B into this forum? If you put some borders around the cells before copying it will come out quite reasonable

Hi ,

Please find below a few lines of the table, I believe this is how you would like me to have it setup for this marco.

Thanks in advance.

Image Name	Product Code	Image
DPMJX8235.jpg	DPMJX8235
DPMJX8234.jpg	DPMJX8234
DPMA81026.jpg	DPMA81026
DPMA81041.jpg	DPMA81041
DPMA81044.jpg	DPMA81044
DPMA81032.jpg	DPMA81032
DPMA81241.jpg	DPMA81241
DPMA81095.jpg	DPMA81095
DPMA81098.jpg	DPMA81098

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[sijpie]

Have you ever tried to step through your code? Open the VBA editor click somewhere in the sub and then press F8 key. As you continue to press F8 each line in turn is executed. This is called step through. Now whie you are doing this, you can hover your mouse over variables to see there value. So let's look at the code I have written:

 sPicPath = "F:\PHOTO\ALL PHOTO\" ' C:\Users\SJR\Desktop\Product Images
    sExt = ".jpg"

Set rProd = Range("A25")    ' column A contains image name
'Start inserting pictures while sProd  isn't empty
DoWhile rProd.Value <> vbNullString
        sFileName = rProd.Value
' put images in column C, as B contains product code
With rProd.Offset(0, 2)
            .RowHeight = 100
            .ColumnWidth = 25
EndWith
' by using the following construction we are _
          working with the new image
With ActiveSheet.Pictures.Insert(sPicPath + sFileName + sExt)

It starts of settng the path in sPiPath and the image extension in sExt

Then it sets the rProd object to the Range A25.
Now it sets the sFileName to the value in A25, which in your latest version is DPMJX8235.jpg
Next it sets the cell size for where the picture has to come
Lastly it (tries) to insert an image found at
sPicPath + sFileName + sExt
or
"F:\PHOTO\ALL PHOTO\" & DPMJX8235.jpg & .jpg
which is one .jpg too many!

So either you leave out the sExt or you have the image names without the extension in A