1

I need a quick way to insert images in cells. I've seen you can do this by inserting comments but it is a slow process.

Can this be done by VBA or a script that would add the pictures from a folder into the desired cells?

Community
  • 1
  • 1
Victor York
  • 1,621
  • 4
  • 20
  • 55
  • 2
    This is a good start : http://stackoverflow.com/questions/12936646/how-to-insert-a-picture-into-excel-at-a-specified-cell-position-with-vba . But you'll need to identify which pictures goes to which cell, do you have any way to do that? – R3uK Jan 24 '17 at 11:05

2 Answers2

0

Take a look at the code below:

Public Sub sampleCode()
Dim WB As Workbook
Dim targetWS As Worksheet
Dim targetCell As Range
Dim imageCounter As Long
Dim sourceFolderPath As String
Dim dirStr As String
Dim imageObj As Picture

Set WB = ThisWorkbook
Set targetWS = WB.Sheets(1)

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select the folder that contains the pictures."
    .Show
    sourceFolderPath = .SelectedItems(1) & "\"
End With

dirStr = Dir(sourceFolderPath)
Do Until dirStr = ""
    If Right(dirStr, 3) = "png" Or Right(dirStr, 3) = "jpg" Then
        imageCounter = imageCounter + 1
        Set imageObj = targetWS.Pictures.Insert(sourceFolderPath & dirStr)
        Set targetCell = targetWS.Range("A" & imageCounter)
        With targetCell
            .ColumnWidth = imageObj.Width / 5.4636515404462
            .RowHeight = imageObj.Height
            imageObj.Top = .Top
            imageObj.Left = .Left
        End With
    End If
    dirStr = Dir
Loop

End Sub

Regards, TheSilkCode

TheSilkCode
  • 366
  • 2
  • 11
0

Thanks, I made a template to insert image into cell according to other cell value : https://www.youtube.com/watch?v=qTUWCk7NpXk

kadrleyn
  • 364
  • 1
  • 5