Insert All Pictures In Folder Excel VBA Macro

In a previous post, we saw how to insert a picture from the web in Excel: Insert Picture From Web URL. We can insert a local picture using the same method, just replacing the URL with the file name and drive path where is located. In this post, we see how to insert all the pictures in a given folder into Excel using VBA macros. The macro inserts the pictures in a given cell or range and resizes them accordingly. We can also decide whether to insert the source information or just make an independent copy of the pictures.

Macro code:

 
  Sub InsertAllPicturesInFolder()
      Dim MyDialog As FileDialog, MyFolder As String, MyFile As String, MyPic As String
      Dim r As Integer, x As Integer, y As Integer, w As Integer, h As Integer
 
      Set MyDialog = Application.FileDialog(msoFileDialogFolderPicker)
      If MyDialog.Show = -1 Then
          MyFolder = MyDialog.SelectedItems(1) & Application.PathSeparator
          MyFile = Dir(MyFolder)
 
          Do While MyFile <> ""
              r = r + 1
              Cells(r, 1).Value = MyFile
              With Cells(r, 2)
                  .RowHeight = 42
                  x = .Left
                  y = .Top
                  w = .Width
                  h = .Height
              End With
       
              MyPic = MyFolder & MyFile
              ActiveSheet.Shapes.AddPicture MyPic, msoFalse, msoTrue, x, y, w, h
              MyFile = Dir
          Loop
      End If
  End Sub
 

Macro explained:

• First, we declare a file dialog object variable (MyDialog) and several string variables to store the folder (MyFolder) and file (MyFile) names, and the concatenation of both (MyPic). Then we declare various numerical variables to store the row counter (r) and the position coordinates and dimension of the range where the picture is inserted.
• We display a dialog box and define a dialog object to select a folder (msoFileDialogFolderPicker) assigned to the variable MyDialog. Then we check if a folder was picked with Show, in which case the result is -1 (otherwise it would be 0).
• Then we assign the selected folder to the variable MyFolder, followed with a path separator (which is usually a back slash “\”) to concatenate with the file name later.
• Next, we use the Dir function to get the files in that folder. We could also specify the type of files we want to target. For example, we could target a certain picture type as indicated below.

 MyFile = Dir(MyFolder & "*.png*")

• Alternatively, we may want to set a condition to check for various picture extensions in case the folder has other type of files. That would go inside the Do loop, and the code could look like this.

 MyFileSplit = Split(MyFile, ".")

 MyFileExtension = MyFileSplit(UBound(MyFileSplit))

 If MyFileExtension = "png" Or MyFileExtension = "jpg" Or MyFileExtension = "bmp" Then

     '--- code to insert the picture here

 End If

• Now we loop through the files in that folder and do the following:
• Increment the row counter r by one
• Add the file name in the corresponding row of column A
• Format the row height of the recipient cell in column B to fit the picture
• Get the coordinates and dimensions of the recipient cell in column B
• Insert the picture as per those coordinates and dimensions (we set the LinkToFile to False and SaveWithDocument to True, so the picture is embedded in the Excel workbook)
• Move to the next file with Dir
• Finally, remember to close the Do loop and end the If statement that checks if a folder was selected in the file dialog box.

This is how we Insert All Pictures In Folder into Excel using VBA macros.

Other examples:

• How to Clear Cells Based On Value with Excel VBA macros
• How to Add Conditional Formatting To Unique Values with Excel VBA macros
• How to Send Multiple Emails with Excel VBA macros