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:

VBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

To Add or Insert Multiple objects from a folder in Microsoft Excel, you need to follow the steps below:

  • Create the object of FileSystemObject(Link)
  • Create Folder object using FileSystemObject and GetFolder (link) method and count the number of files.
  • Get the files Object
  • Run the Loop for each files in folder
  • Insert the File objects , using OLE objects.
  • Insert the added files name list in another sheet(just for reference)
  • Save the workbook

Read moreVBA-Excel: Add/Insert multiple objects from a folder in an Excel Document.

FileSystemObject : GetExtensionName Method

Description:

Returns extension of a file, return empty string if file doesn’t exist.

Format : 

objectOfFileSystemObject. GetExtensionName(filepath)

  • objectOfFileSystemObject : As the names says, it’s a FileSystemObject.
Arguments: 
    • filepath
      • Mandatory
      • Type:     String
      • filepath , whose extension to be return.

    Read moreFileSystemObject : GetExtensionName Method

    FileSystemObject : MoveFile Method

    Description: 

    This function moves a file from one location to another.

    Format : 

    objectOfFileSystemObject. MoveFile (source, destination);

    Arguments: 
    • objectOfFileSystemObject : As the name says, it’s a FileSystemObject.

    As the name says, it’s a FileSystemObject.

    FileSystemObject:OpenTextFile Method

    FileSystemObject:OpenTextFile Method

    Description :

    This function opens a file and returns a TextStream object and later you can use that TextStream object to perform various activities on that file like reading or writing

    Format :

    objectOfFileSystemObject. OpenTextFile(filename[, iomode[, create[, format]]])

    Arguments: 

    FileSystemObject : CreateTextFile Method

    Description: 

    This function creates a file and returns a TextStream object and later you can use that TextStream object to perform various activity on that file like reading or writing

    Format :

    objectOfFileSystemObject. CreateTextFile (filename[, overwrite[, unicode]])

    • objectOfFileSystemObject : As the names says, it’s a FileSystemObject.
    Arguments: 
      • filename
        • Mandatory
        • Type:     String
        • File will be created with the same name what you will pass here.

      Read moreFileSystemObject : CreateTextFile Method