CODEWITHSUNDEEP
vbaexcel
user1783504
user1783504

Reputation: 453

VBA - Excel - How to retrieve photos on matching file name and place them in cell

I have an excel sheet where column A has a list of product codes. I also have a folder with pictures of each product and file name of the pictures are the product code. I would like to place the picture of each product in column B beside their respective codes. If possible, I would also like to reformat the pictures so they fit in the cell.

I don't really know where to start and any help would be greatly appreciated! thanks

Upvotes: 2

Views: 15931

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Here is a code to start. Please test it out in your side. 

 Sub AddPictures()
 Dim myPic As Picture
 Dim wkSheet As Worksheet
 Dim myRng As Range
 Dim myCell As Range
 Dim rowCount As Long
 Dim rowCount2 As Long

     Set wkSheet = Sheets(2) ' -- Change to your sheet

    '-- The usual way of finding used row count for specific column
    rowCount2 = wkSheet.Cells(wkSheet.Rows.Count, "C").End(xlUp).Row

    If rowCount2 <> 0 Then
        Set myRng = wkSheet.Range("C2", wkSheet.Cells(wkSheet.Rows.Count, "C").End(xlUp))

        For Each myCell In myRng.Cells
               If Trim(myCell.Value) = "" Then
                    MsgBox "No file path"
               ElseIf Dir(CStr(myCell.Value)) = "" Then
                    MsgBox myCell.Value & " Doesn't exist!"
               Else
                    myCell.Offset(0, 1).Parent.Pictures.Insert (myCell.Value)
                    Set myPic = myCell.Parent.Pictures.Insert(myCell.Value)

                    With myCell.Offset(0, 1)  '1 columns to the right of C ( is D)
                        '-- resize image here to fit into the size of your cell
                        myPic.Top = .Top
                        myPic.Width = .Width
                        myPic.Height = .Height
                        myPic.Left = .Left
                        myPic.Placement = xlMoveAndSize
                    End With
               End If
        Next myCell

    Else
        MsgBox "There is no file paths in your column"
    End If
End Sub

Output:

enter image description here

PS: set range, file paths according to your values. If you need to search entire sheet for used REAL columns and rows, let me know. I can share a function for that. Right now you only need to find used rows in the specific file-paths column, so the above typical used row count line is good enough.

Upvotes: 3

Related Questions