Adding a loop to macro to insert pictures [Solved/Closed]

Report
-
M_TheJax
Posts
1
Registration date
Thursday March 5, 2015
Status
Member
Last seen
March 5, 2015
-
Hello,
Could someone please help me in writing a loop in this macro in excel :
I have an excel sheet where it contains the name of the pictures in column B. I have written a Macro to pick up the name of the picture in column B and insert the actual picture from directory into column A.
I need create a loop to do this depending on the number of pictures in the column B. The number of rows varies from 10 to 1000.
The macro that I have written works perfect for the first row but does not go to the next row since I am not sure how to do it.

Thanks for your help in advance.
Vida


Please see the macro that I’ve written:-

Sub Picture()
Range("A6").Select 'This is where picture will be inserted
Dim picname As String
picname = Range("B6") 'This is the picture name
ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
.Left = Range("A6").Left
.Top = Range("A6").Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 80#
.ShapeRange.Rotation = 0#
End With

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

1 reply

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
I have not tested your macro, but for looping you can try some thing like this

Assumptions
1. picture names are found in col B starting at B2
2. You want to paste each picture at different location
3. where to paste the picture identified in col B, is found in col C starting at C2


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 = Cells(lThisRow, 3)
        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:\Users\vbayat\My Documents\vidabayat\re-market\" & 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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 5245 users have said thank you to us this month

rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756 > vbayat
Posts
5
Registration date
Thursday February 18, 2010
Status
Member
Last seen
February 19, 2010

Are you saying that on a sheet
1. On cell b2 would be the picture name
2. on cell a2 you would insert the picture

So the row where the name is, is the row where the picture is going to be pasted ?
If that is the case


Change this line
pasteAt = Cells(lThisRow, 3)
to

pasteAt = lThisRow

See if it works for you. Also please be aware that there cannot be a blank value in col B as the macro will terminate its execution as soon as it encounters such a cell.
vbayat
Posts
5
Registration date
Thursday February 18, 2010
Status
Member
Last seen
February 19, 2010
> rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016

Hello, This now works. Thank you very much.
Is there a way to alter the code so you can have a blank or a code picture name it cannot find.

Also, normally all the cells are squished together really tightly. Now that we are adding a picture they will become a bit broader. Is there a way for the cell height and width to auto adjust with that of the picture. Thank you so much!
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
M_TheJax
Posts
1
Registration date
Thursday March 5, 2015
Status
Member
Last seen
March 5, 2015

I am essentially a novice w VBA for Excel. So far, so good BUT I just can't make my brain figure out something that has to be so simple....I need to do the opposite with the code you've shown. In other words, I need the path to the image to be what is collected from B6. I have the picture names. I just don't see what I'm doing wrong within the (). I used Dim path As String, then put the word path first in the brackets. I think it's the quotations within the brackets that are throwing me off.

This obviously doesn't work:

ActiveSheet.Pictures.Insert("path" & "image.jpg").Select
or
ActiveSheet.Pictures.Insert(path & "image.jpg").Select

Please have mercy.