Link data with image

Closed
Pooja V Posts 4 Registration date Wednesday October 30, 2013 Status Member Last seen November 11, 2013 - Nov 9, 2013 at 06:39 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 13, 2013 at 03:21 PM
Hello,

I have C:\Photo folder with all jpeg images.
All the names are unique and exactly matches with my
product code number.

In excel 2007 I want to match my code number with the
image name and display the image.

My range for giving the code number (without .jpeg extension) C8:C28. Anywhere
from C8 to C28 I will mention from 1 to max 10 code
numbers. The first five image (as per code number sequence )
will display from D4 to H4 ( 0.75 x 0.75 inch )
and second five from D6 to H6 ( 0.75 x 0.75 inch ).

IMPORTANT: In case the code number doesnot have the matching
image then the image box should be blank.

thanks
Pooja V



Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 10, 2013 at 09:39 AM
0
Pooja V Posts 4 Registration date Wednesday October 30, 2013 Status Member Last seen November 11, 2013
Nov 11, 2013 at 06:06 AM
hello rizvisa1

This was the changes I had done in you code
I had right clicked on sheet1 - View Code and pasted the code.

Sub Picture()
Dim picname As String

Dim pasteAt As Integer
Dim lThisRow As Long

lThisRow = 2

Do While (Cells(lThisRow, 2) <> "")

'Range("A6").Select 'This is where picture will be inserted
pasteAt = lThisRow
Cells(pasteAt, 1).Select 'This is where picture will be inserted

'Dim picname As String
'picname = Range("B6") 'This is the picture name
picname = Cells(lThisRow, 2) 'This is the picture name

ActiveSheet.Pictures.Insert("C:\Photo\" & picname & ".jpg").Select 'Path to where pictures are stored
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
'.Left = Range("A6").Left
'.Top = Range("A6").Top
.Left = Cells(pasteAt, 1).Left
.Top = Cells(pasteAt, 1).Top

.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 80#
.ShapeRange.Rotation = 0#
End With

lThisRow = lThisRow + 1

Loop

Range("A10").Select
Application.ScreenUpdating = True

Exit Sub

ErrNoPhoto:
MsgBox "Unable to Find Photo" 'Shows message box if picture not found
Exit Sub
Range("B20").Select

End Sub

I changed the path . But when I enter any name in B column , I am not
getting any pic.. Is there any mistake I have done in the path.

Also can the code be changed as per my requirement, because in my case
there will be blanks at some place in the range and the are where I want the
pics is also different.

Thanks
Pooja
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 13, 2013 at 03:21 PM
current macro is "on demand" run. If you want to do it on entry, a little tweak is needed. Also you would need to leverage the events to do that
0