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 man­u­ally, insert and resize the image and drag it to the par­tic­u­lar cell, but think of a sce­nario where you have more than 100 images or may be more. I am sure you don’t want to do it man­u­ally. IF you are look­ing for solu­tions for prob­lems like this, this is the tuto­r­ial for you.

This tuto­r­ial will teach you about how to insert mul­ti­ple images from a folder into your excel.

Steps:

  • Open a folder(which con­tains images) using “Scripting.FileSystemObject”.
  • Get the files in it.
  • Make a loop for all the files
  • Check If files name con­tains “jpg”, “jpeg”, “gif” (You can add more),
  • Acti­vate and resize the cell where you want to insert your image.
  • call Insert func­tion by pro­vid­ing the com­plete path and cell number.
  • In Insert func­tion, call ActiveSheet.Pictures.insert(piture path).
  • resize the image and set it to the spec­i­fied cell.

Open a folder(which con­tains images) using ” Scripting.FileSystemObject”.

Fold­er­path = “C:\Users\Sumit Jain\Pictures”

Set fso = CreateObject(“Scripting.FileSystemObject”)

NoOf­Files = fso.GetFolder(Folderpath).Files.Count

Get the files in it.

Set list­files = fso.GetFolder(Folderpath).Files

Make a loop for all the files

For Each fls In listfiles

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

If (InStr(1, str­Comp­FilePath, “jpg”, vbTextCom­pare) > 1 Or InStr(1, str­Comp­FilePath, “jpeg”, vbTextCom­pare) > 1 Or InStr(1, str­Comp­FilePath, “png”, vbTextCom­pare) > 1) Then

Acti­vate 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 func­tion by pro­vid­ing the com­plete path and cell number.

str­Comp­FilePath = Fold­er­path & “\” & Trim(fls.Name)

Call insert(strCompFilePath, counter)

In Insert func­tion, call ActiveSheet.Pictures.insert(piture path).

ActiveSheet.Pictures.insert(PicPath)

resize the image and set it to the spec­i­fied cell.

With .ShapeRange

.Lock­Aspec­tRa­tio = msoTrue

.Width = 50

.Height = 70

End With

.Left = ActiveSheet.Range(“B” & counter).Left

.Top = ActiveSheet.Range(“B” & counter).Top

.Place­ment = 1

.Print­O­b­ject = True

 

Com­plete 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 Mul­ti­ple 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 some­body else is not dis­play­ing pic­tures. Only links to the files are saved inside of the file, not real pic­tures. I need to make macro put pic­tures inside of the excel sheet so that those pic­tures are inde­pen­dent of the source after­wards. Is it pos­si­ble??? Thanks a lot for any idea.

  2. prakaskh says:

    where is the remain­ing pro­gram like sub insertpic().…End sub. This pro­gram starts only with Func­tion key­word.. Could you please pro­vide the remain­ing part of this pro­gram to insert all pic­tures 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 Col­umn A and insert the Pic­ture in Col­umn B.
    Your macro inserts the name of the image in Col A and the Pic­ture in Col­umn B. Any idea of the revised code? I tried search­ing but could not get a suit­able code…Thanks

    • SJ says:

      You will have to mod­ify the code a bit, get the num­ber of rows using Use­dRange, run the loop and search for every image in the given loca­tion and if found then paste it in the next col­umn. The core part of the already writ­ten code will not change. please let me know if things are not clear

  4. Ale says:

    Hi,
    I can­not run this code in Mac, because in the line that you are cre­at­ing an object:
    Set fso = CreateObject(“Scripting.FileSystemObject”)

    There is a error mes­sage: “ActiveX com­po­nent can’t cre­ate object” because Mac doesn’t have this com­po­nent.
    Do you know an alter­na­tive 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 com­puter, all images will not be shown.. do you have a solu­tion for this case?

    Thank you.
    Santo

    • SJ says:

      I have checked by open­ing the file in dif­fer­ent sys­tem and its work­ing fine, could you tell all the steps you are following.

  6. Greg says:

    Thanks for this great macro. Can some­one tell me how to revise so it starts insert­ing the images in a spec­i­fied cell.

    • Karl says:

      first i did a lot of work to get this run — but then i tried with image-xls. runs really per­fect. it also inserts PDFs if you like, or imports pic­tures from my online-shop direc­tory. i first tried with a free demo.

  7. Karl says:

    there is an great tool “image-xls”, which inserts as much pitc­tures you like. and you’re able sort and fil­ter your Excel spread sheet includ­ing the pictures

  8. Wasim X says:

    thanks a lot SJ for pro­vid­ing help­ful arti­cle, — cheers

  9. Sandeep says:

    Hello thanks lot SJ for such a great help. I need your help in fix­ing a small help.

    I want to copy those images hor­i­zon­tally rather than ver­ti­cally… I mean like copy­ing 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 pic­tures in the folder to the sheet you des­ig­nate. I copied it from the fol­low­ing link: Excel-VBA : Insert Mul­ti­ple Images from a Folder to Excel Cells Perpa […]

  2. October 27, 2015

    […] See if the fol­low­ing links will help you get going… [Photo Com­ment] Prob­lem: Local File Only Excel-VBA : Insert Mul­ti­ple Images from a Folder to Excel Cells […]

  3. December 18, 2015

    […] ALL the pic­tures in the folder to the sheet you des­ig­nate. I copied it from the fol­low­ing link: Excel-VBA : Insert Mul­ti­ple 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: