Macro for insert picture

Closed
Posts
3
Registration date
Thursday October 29, 2015
Status
Member
Last seen
October 30, 2015
-
 Blocked Profile -
Hello,
Please help
i have to insert item pictures in column A i have item code in column B. All pictures saved in D drive folder name pictures. filname start with code number but other information like size also written in file name.Below macro works fine if i have file name only as code number.
i want modiffication in this macro so it will insert pictures which file name start with "item code" but ignore later part of file name.(code*.jpg).

Thanks


Sub Picture()
Dim picname As String

Dim pasteAt As Integer
Dim lThisRow As Long

lThisRow = 2

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


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


picname = Cells(lThisRow, 2) 'This is the picture name

present = Dir("C:\Users\Administrator\Desktop\LC\" & picname & ".jpg")

If present <> "" Then

ActiveSheet.Pictures.Insert("C:\Users\Administrator\Desktop\LC\" & 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 = 130#
.ShapeRange.Rotation = 0#
End With



Else
Cells(pasteAt, 1) = "No Picture Found"
End If

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


2 replies


OK do you see the relative path to the picture? Have you tried this yet since present is the path?

present = Dir("C:\Users\Administrator\Desktop\LC\" & picname & ".jpg")

ActiveSheet.Pictures.Insert(present)

Posts
3
Registration date
Thursday October 29, 2015
Status
Member
Last seen
October 30, 2015

Thanks
i can change file path, but main problem is i have only item_code in other column (like 110002347) but file name has more character (like 110002347, 2pc, 12x50x10cm).

Thanks
Blocked Profile
NOT the path, use your variable. You set the variable as the file name and the path. USE THE VARIABLE!