Insert multiple pictures

[PatriciaM]

Hello
I need your help.
I have hundreds of photos to insert into an excel sheet.
Each photograph is an article. The code of the article is the filename of each photo.
I need a macro that inserts the picture in column B on the same line of code in this article in column C.
The cell where the photo has got to be 30x100 (width x height)
This is my typical excel list


ScreenHunter_03 Feb. 02 15.23.jpgScreenHunter_04 Feb. 02 15.28.jpg


Someone help me?

Patricia

[mrice]

Try this macro

Option Explicit

Sub AddPictures()
Dim N As Long

Const SourceFolder = "C:\TEMP\Test\Images\"
For N = 2 To Cells(Rows.Count, 3).End(xlUp).Row
    ActiveSheet.Pictures.Insert(SourceFolder & Cells(N, 3) & ".jpg").Select
    Selection.Top = Rows(N).Top + 2
    Selection.Height = Rows(N).RowHeight - 4
    Selection.Left = Columns(2).Left + 2
Next N
End Sub

You will need to size the cells before running this as it fits the image to the cell. Also edit the Source Folder string and check if your images ae .jpg - adjust if not.

Open the VBA editor Alt F11. Inset a new module (Insert - Module) and paste in the above. Go back to the sheet (Alt F11). Run the macro from the view ribbon.

[PatriciaM]

Hello
Already thank you for answering.
I did as you said but gave this error ScreenHunter_08 Feb. 02 17.08.jpg

[mrice]

It's possible that there is some sort of mismatch between what you have in the cell in column C and the actual name of the file.

To check this, I would suggest....

opening the immediate pane in the VBA editor (Control G)
Typing in a question mark (?) followed by the string copied from the code SourceFolder & Cells(N, 3) & ".jpg"
With the cursor on the same line, hit return

You should get a line which shows what the expression evaluates to. My guess is that this will differ in some way from the path that you were expecting.

If this is not the case, could you attach a few picture files to a reply so that I can experiment?

[PatriciaM]

Hello
Sorry for the delay in responding. The company I work for had some problems and the matter remained in the background.
Annex now the file that I use for stock and want to insert the photos.


Stock List.jpg
13542.jpg
13543.jpg
13545.jpg
13544.jpg

[mrice]

Try this variation

Option Explicit


Sub AddPictures()
Dim N As Long

Const SourceFolder = "C:\TEMP\Test\Images\"
For N = 3 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(N, 2).Select
    With ActiveSheet.Pictures.Insert(SourceFolder & Cells(N, 3) & ".jpg")
        .Top = Rows(N).Top + 2
        .Height = Rows(N).RowHeight - 4
        .Left = Columns(2).Left + 2
    End With
Next N
End Sub

[PatriciaM]

Hello
Thank you. For now works fine ... now I have a huge task ahead of me.

[PatriciaM]

Hello
The macro inserts the image. It is possible that the macro create a hyperlink on the image to open the file that caused it?
Some of the image are very small ... so with one click could open the original image.

Thank you.

Patricia

[chhitra]

hello, it's working on 2010 excel?
as I have error - Run-time error '1004': unable to get the insert property of the pictures class
thank you!

[mrice]

Patricia

Try this slight modification.

Option Explicit

Sub AddPictures()
Dim N As Long

Const SourceFolder = "C:\TEMP\Test\Images\"
For N = 3 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(N, 2).Select
    With ActiveSheet.Pictures.Insert(SourceFolder & Cells(N, 3) & ".jpg")
        .Top = Rows(N).Top + 2
        .Height = Rows(N).RowHeight - 4
        .Left = Columns(2).Left + 2
    End With
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Shapes(ActiveSheet.Shapes.Count), Address:=SourceFolder & Cells(N, 3) & ".jpg"
Next N
End Sub

[PatriciaM]

Hello
The macro only entered a photo and gave the error in the line of code you see in the image that I attached
But the link is inserted

Patricia


ScreenHunter_01 Apr. 04 15.45.jpg

[PatriciaM]

Hello
I almost forgot.
In the first code you sent I made a change because the macro does not work.
I inserted a line of code "ON ERROR RESUME NEXT"
the macro ran perfectly well even though I do not know what I did ...
The second macro without my line of code gives error.
With the line enters the picture but not the links.

Option Explicit


Sub AddPictures()
Dim N As Long
On Error Resume Next
Const SourceFolder = "C:\Users\ESCRITORIO\Desktop\pics\"
For N = 3 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(N, 2).Select
    With ActiveSheet.Pictures.Insert(SourceFolder & Cells(N, 3) & ".jpg")
        .Top = Rows(N).Top + 2
        .Height = Rows(N).RowHeight - 4
        .Left = Columns(2).Left + 2
    End With
        

Next N
End Sub 

[mrice]

It seems to work OK for me. The only thing that I can think of is that you have a filename in your sheet which doesn't exist. It would be worth checking the spelling and looking for leading/trailing spaces.

[PatriciaM]

Hello
Ok ... now I understand what you did ... there is no problem I adapt my way of working with macro ... gives just a little more work than what I was thinking but still your macro will make possible what I want.


Obrigadoooooooooo (many thank you in Portuguese)

[mrice]

You could try this variation.

Sub AddPictures()
Dim N As Long
On Error Resume Next
Const SourceFolder = "C:\temp\test\"
For N = 3 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(N, 2).Select
    
    ActiveSheet.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 100
    ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Fill.UserPicture SourceFolder & Cells(N, 3) & ".jpg"

    
    
    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
        .Top = Rows(N).Top + 2
        .Height = Rows(N).RowHeight - 4
        .Left = Columns(2).Left + 2
        .Width = Columns(2).Width - 4 + 2
    End With
         
    
Next N
End Sub