Automatically insert an image into a spreadsheet by using a macro

---

In this blog post I will illustrate how to insert an image into a spreadsheet by using a macro.

STEP 1] Save your spreadsheet as macro enabled workbook and go to Developer > Visual Basic >Insert> Module 

STEP 2] Insert the code below into the module

Sub InsertImage()
 
 Dim Pict() As Variant
 Dim ImgFileFormat As String
 Dim PictCell As Range
 Dim lLoop As Long
 Dim sShape As Picture
 ActiveSheet.Protect False, False, False, False, False  ImgFileFormat = "All Picture Files(*.emf;*.wmf;*.jpg;*.jpeg;*.jfif;*.jpe;*.png;*.bpm;*.gif;*.gfa;*.emz;*.wmz;*.pcz;*.tif;*.tiff;*.cgm;*.eps;*.pct;*.pict;*.wpg;*.pcd;*.pcx;*.cdr;*.fpx;*.mix), *.bmp"
 
 'The code above allows you to upload images in nearly all formats including JPG and PNG
 Pict = Application.GetOpenFilename(ImgFileFormat, MultiSelect:=True)
 If Not IsArray(Pict) Then
 Debug.Print "No files selected."
 Exit Sub
 End If
 
 Set PictCell = Selection.Cells(1)
 For lLoop = LBound(Pict) To UBound(Pict)
 Set sShape = ActiveSheet.Pictures.INsert(Pict(lLoop))
 With sShape
 If .Height < 408.75 Then
 PictCell.EntireRow.RowHeight = .Height
 End If
 .Top = PictCell.Top
 .Left = PictCell.Left
 End With
 Set PictCell = PictCell.Offset(1)
 Next lLoop
  
Application.ScreenUpdating = False
Application.DisplayAlerts = False

End Sub

Step 3] Exit the Visual Basic editor and left click on the cell on which you would like the image inserted. Then run the VBA code by going to Developer>Macros.

Upon clicking the macro will prompt you to select a file form your device and as soon as you do so and click okay the image will appear in that selected cell. I used cell A3 as my destination cell and here is the result of running the macro.

​Alternatively, if you would like to avoid clicking on the cell on which you want the image to be inserted you could  use a code similar to the one below in order to have a particular cell automatically selected prior to having the main code run. This will help you save time and will let you indicate a fixed location for the image.

Sub selectCell()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Sheets("Home").Select
Range("A3").Select
End Sub

This code could easily be created by recording a macro as well. Note that  in the code above, "Home" is the name of the particular tab and "A3" is the cell in which we want the image to be inserted.

​Once you insert the two pieces of code above into the module tab go to the "Thisworkbook" within the VBA editor and Call the two private subs as shown in the screenshot below and then run the sub CallImage ()

---