Excel-VBA : Insert Multiple Images from a Folder to Excel Cells

Say you have many images in a folder and you want to insert all these images in your excel work book, one image in one cell. You can do it manually, insert and resize the image and drag it to the particular cell, but think of a scenario where you have more than 100 images or may be more. I am sure you don’t want to do it manually. IF you are looking for solutions for problems like this, this is the tutorial for you.

This tutorial will teach you about how to insert multiple images from a folder into your excel.

Steps:

  • Open a folder(which contains images) using “Scripting.FileSystemObject”.
  • Get the files in it.
  • Make a loop for all the files
  • Check If files name contains “jpg”, “jpeg”, “gif” (You can add more),
  • Activate and resize the cell where you want to insert your image.
  • call Insert function by providing the complete path and cell number.
  • In Insert function, call ActiveSheet.Pictures.insert(piture path).
  • resize the image and set it to the specified cell.

Open a folder(which contains images) using “ Scripting.FileSystemObject”.

Folderpath = "C:\Users\Sumit Jain\Pictures"

Set fso = CreateObject("Scripting.FileSystemObject")

NoOfFiles = fso.GetFolder(Folderpath).Files.Count

Get the files in it.

Set listfiles = fso.GetFolder(Folderpath).Files

Make a loop for all the files

For Each fls In listfiles

Check If files name contains “jpg”, “jpeg”, “gif” (You can add more),

If (InStr(1, strCompFilePath, "jpg", vbTextCompare) > 1 Or InStr(1, strCompFilePath, "jpeg", vbTextCompare) > 1 Or InStr(1, strCompFilePath, "png", vbTextCompare) > 1) Then

Activate and resize the cell where you want to insert your image.

Sheets("Object").Range("A" & counter).Value = fls.Name

Sheets("Object").Range("B" & counter).ColumnWidth = 25

Sheets("Object").Range("B" & counter).RowHeight = 100

Sheets("Object").Range("B" & counter).Activate

Call Insert function by providing the complete path and cell number.

strCompFilePath = Folderpath & "\" & Trim(fls.Name)

Call insert(strCompFilePath, counter)

In Insert function, call ActiveSheet.Pictures.insert(piture path).

ActiveSheet.Pictures.insert(PicPath)

resize the image and set it to the specified cell.

With .ShapeRange

.LockAspectRatio = msoTrue

.Width = 50

.Height = 70

End With

.Left = ActiveSheet.Range("B" & counter).Left

.Top = ActiveSheet.Range("B" & counter).Top

.Placement = 1

.PrintObject = True

Complete Code:

Insert Multiple Images from a Folder to Excel Cells
Insert Multiple Images from a Folder to Excel Cells

23 thoughts on “Excel-VBA : Insert Multiple Images from a Folder to Excel Cells”

  1. Hi. Nice macro, I am using some other code, but with same PROBLEM. Saved file sent to somebody else is not displaying pictures. Only links to the files are saved inside of the file, not real pictures. I need to make macro put pictures inside of the excel sheet so that those pictures are independent of the source afterwards. Is it possible??? Thanks a lot for any idea.

    Reply
  2. where is the remaining program like sub insertpic()….End sub. This program starts only with Function keyword.. Could you please provide the remaining part of this program to insert all pictures into the excel?

    Thanks.

    Reply
  3. Hi Sumit, I wish to have a macro which would look the name of the file in Column A and insert the Picture in Column B.
    Your macro inserts the name of the image in Col A and the Picture in Column B. Any idea of the revised code? I tried searching but could not get a suitable code…Thanks

    Reply
    • You will have to modify the code a bit, get the number of rows using UsedRange, run the loop and search for every image in the given location and if found then paste it in the next column. The core part of the already written code will not change. please let me know if things are not clear

      Reply
  4. Hi,
    I cannot run this code in Mac, because in the line that you are creating an object:
    Set fso = CreateObject(“Scripting.FileSystemObject”)

    There is a error message: “ActiveX component can’t create object” because Mac doesn’t have this component.
    Do you know an alternative code where I could run on Mac, please?

    Thank you,
    Ale

    Reply
  5. Hi.. thx for this macro.. but I found that the images are only linked and not paste inside the excel.. when I open the excel in other computer, all images will not be shown.. do you have a solution for this case?

    Thank you.
    Santo

    Reply
    • first i did a lot of work to get this run – but then i tried with image-xls. runs really perfect. it also inserts PDFs if you like, or imports pictures from my online-shop directory. i first tried with a free demo.

      Reply
  6. there is an great tool “image-xls”, which inserts as much pitctures you like. and you’re able sort and filter your Excel spread sheet including the pictures

    Reply
  7. Hello thanks lot SJ for such a great help. I need your help in fixing a small help.

    I want to copy those images horizontally rather than vertically… I mean like copying images to B1,C1, D1cells instead of A1,A2,A3 cells.
    Kindly give your input in solving/fixing it

    Reply
  8. PLEASE CLEAR HOW TO INSERT MULTI IMAGE IN EXCEL WITH NAME MACH & DIFFERENT CELL OR MARGE CELL. MOBILE NO. 9829816106

    Reply

Leave a Reply to SJ Cancel reply