Excel Picture Inserting Macro Help - Urgent

Solved/Closed
nadimn Posts 3 Registration date Tuesday April 27, 2010 Status Member Last seen April 28, 2010 - Apr 27, 2010 at 04:34 PM
 jackcluseau - Dec 4, 2014 at 11:26 PM
Dear Rizvisa,

please consider this little urgent and be kind to help me please

I m trying to insert picture in excel column using macro which takes the file name refrence from the B2 cell and insert picture in A2 cell.
for example: if B2 cell is N235 then the picture which will be inserted in A2 would be N235.jpg from the given path in Macro. This is working fine EXCEPT when there is no picture in the folder with the the same name as in folder.
I get a runtime error 1004 and Macro stops and can not fill picture after that cell.
I m using following Macro.
----------------------------------------------------------------------------------------------------------------
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

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

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
----------------------------------------------------------------------------------------------------------------

Could you please help me in solving this problem. I want it like this, if there is no picture with the same name then "no picture found" should be displayed in the cell. or any dummy picture in all those cell where there is no picture in the folder with the same name as in the cell.

for example: if in B5 value is Z028 and if the picture Z028.jpg is not there in my LC folder (as given above in macro) then it should show "no picture found" and continue adding all other pictures till end.

I would be grateful to you.

Thanks in advance

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 27, 2010 at 10:15 PM
Try this. New lines are highlighted



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
6
nadimn Posts 3 Registration date Tuesday April 27, 2010 Status Member Last seen April 28, 2010
Apr 27, 2010 at 11:20 PM
Dear Rizvisa,

thank you so much for the help. BUT still there is problem

when I m running this new macro, it pastes pictures and pastes "no picture found" only in first possible cell then it get stuck.both excel visual basic editor get hangs and u can not do anything after that. In task manager it doesnt show not responding thing to excel it shows normal running but actually it is jam..
may be little tweaking here n there required
your m waiting 4 ur quick reply again..
thankyou
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 28, 2010 at 06:03 AM
I made a mistake. The row increment code
lThisRow = lThisRow + 1
was not at right place. Try now
0