Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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:

Sub AddOlEObject()

    Dim mainWorkBook As Workbook

    Set mainWorkBook = ActiveWorkbook
    Sheets("Object").Activate
    Folderpath = "C:\Users\Sumit Jain\Pictures"
    Set fso = CreateObject("Scripting.FileSystemObject")
    NoOfFiles = fso.GetFolder(Folderpath).Files.Count
    Set listfiles = fso.GetFolder(Folderpath).Files
    For Each fls In listfiles
       strCompFilePath = Folderpath & "\" & Trim(fls.Name)
        If strCompFilePath <> "" Then
            If (InStr(1, strCompFilePath, "jpg", vbTextCompare) > 1 _
            Or InStr(1, strCompFilePath, "jpeg", vbTextCompare) > 1 _
            Or InStr(1, strCompFilePath, "png", vbTextCompare) > 1) Then
                 counter = counter + 1
                  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(strCompFilePath, counter)
                Sheets("Object").Activate
            End If
        End If
    Next
mainWorkBook.Save
End Sub

Function insert(PicPath, counter)
'MsgBox PicPath
    With ActiveSheet.Pictures.insert(PicPath)
        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
    End With
End Function

 

Insert Multiple Images from a Folder to Excel Cells

Insert Multiple Images from a Folder to Excel Cells

You may also like...

23 Responses

  1. Maros says:

    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.

  2. prakaskh says:

    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.

  3. Rashid Khan says:

    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

    • SJ says:

      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

  4. Ale says:

    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

  5. Yusak says:

    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

    • SJ says:

      I have checked by opening the file in different system and its working fine, could you tell all the steps you are following.

  6. Greg says:

    Thanks for this great macro. Can someone tell me how to revise so it starts inserting the images in a specified cell.

    • Karl says:

      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.

  7. Karl says:

    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

  8. Wasim X says:

    thanks a lot SJ for providing helpful article, – cheers

  9. Sandeep says:

    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

  10. MANISH GUPTA says:

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

  1. October 27, 2015

    […] ALL the pictures in the folder to the sheet you designate. I copied it from the following link: Excel-VBA : Insert Multiple Images from a Folder to Excel Cells Perpa […]

  2. October 27, 2015

    […] See if the following links will help you get going… [Photo Comment] Problem: Local File Only Excel-VBA : Insert Multiple Images from a Folder to Excel Cells […]

  3. December 18, 2015

    […] ALL the pictures in the folder to the sheet you designate. I copied it from the following link: Excel-VBA : Insert Multiple Images from a Folder to Excel Cells Perpa […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: