How To Insert Pictures in Excel Using a Macro

January 2018

Macros in Excel can perform functions like inserting pictures, copying data from one cell to another, etc. In order to write an effective macro to insert images in excel, you will need to use a loop

This FAQ will walk you through the steps to applying this macro to your spreadsheet in order to have pictures appear.

Use a Macro to Insert Pictures in Excel

In order to use a macro to insert pictures, you must begin with the following assumptions: picture names are found in col B starting at B2; you want to paste each picture at different location; where to paste the picture identified in col B is found in col C starting at C2.

Now, enter the following code:


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

Image: © Microsoft.
Published by aakai1056. Latest update on November 22, 2017 at 06:06 PM by owilson.
This document, titled "How To Insert Pictures in Excel Using a Macro," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
Excel - Find and Replace a Range
Excel - Combining info from 2 sheets