Insert picture with macro

[Akos]

Hi. I need a macro which can insert pictures to excel from local folder. The column "A" contains the value (sku) and i need the pictures to column "B". In the folder the picture names are equal to column A (sku) names.

As there are way more pictures in that folder i need a macro which inserts with the exact sku name. (there are picture names in the folder which names just diff. From eachother with a _1 or etc.. in the end of the file name.

I hope somebody can help me! Thank you.

 

[jolivanes]

What is in Column A? There are multiple possibilities.
1) Path and Picture Name including proper extension like ".jpg" or ".jpeg" or ".bmp" or ".png"
2) Path and Picture Name without extension.
3) Just Picture Name with extension as previously mentioned.
4) Just Picture Name without extension

Do the inserted pictures need to be sized like fit a cell size?
If so, how many cells in height and how many columns in width?

 

[Akos]

What is in Column A? There are multiple possibilities.
1) Path and Picture Name including proper extension like ".jpg" or ".jpeg" or ".bmp" or ".png"
2) Path and Picture Name without extension.
3) Just Picture Name with extension as previously mentioned.
4) Just Picture Name without extension

Do the inserted pictures need to be sized like fit a cell size?
If so, how many cells in height and how many columns in width?

4th option, column A values are exactly the same as the picture names (all pics are jpg)
It could be cool if it can fit in the column but not a must have. (height 150, width 200 but if i can see where can i modify this, i can do it too)

 

[jolivanes]

Please don't quote whole posts. Just clutter we don't need or want.
Regarding the size. Tell us what it needs to be. Either 150 x 200 or Cell Height by Column Width or a combination.

 

[jolivanes]

No error checking included!
Change references where required.
This sets the picture height to row height and width to column width.

Sub InsertPictures_Without_Extension()
Dim sFolder As String
Dim sFile As String
Dim i As Long
Dim w As Worksheet
sFolder = "C:\Folder where pictures are located\"    '<----- Change as needed
Set w = ActiveSheet ' or a specific sheet
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row    '<---- Starts at A2. Change the 2 to a 1 if need to start at A1
    sFile = Dir(sFolder & Cells(i, 1).Value & ".jpg")
        w.Shapes.AddPicture Filename:=sFolder & sFile, _
            LinktoFile:=False, SaveWithDocument:=True, _
            Left:=Cells(i, 2).Left, Top:=Cells(i, 2).Top, _
            Width:=Cells(i, 2).Width, Height:=Rows(i).Height
    sFile = Dir
Next i
End Sub

 

[Akos]

Can you please include error checking? I just ran into one value that dont have picture in that folder, and the macro just ran till that row. But till that, it's working so thank you so much!

 

[jolivanes]

Insert the two lines where indicated.

    sFile = Dir(sFolder & Cells(i, 1).Value & ".jpg")
        If sFile <> "" Then    '<----- Insert this line
            w.Shapes.AddPicture Filename:=sFolder & sFile, _
                LinktoFile:=False, SaveWithDocument:=True, _
                Left:=Cells(i, 2).Left, Top:=Cells(i, 2).Top, _
                Width:=Cells(i, 2).Width, Height:=Rows(i).Height
            sFile = Dir
        End If    '<----- Insert this line
Next i

 

[Akos]

Thank you, working too!