ALL AUTOMATE WORKFLOW EVERYWHERE BUSINESS ENGLISH ARTICLES

How to import images from a selected folder (and its subfolders) into Excel

This VBA script [Import Pictures from folder and subfolders, Retail Price and Product ID] automates the process of matching image files listed in a worksheet with their associated data from another worksheet. It also extracts and populates additional information about the files, such as their size and last modification date. Here’s a detailed breakdown of what this script does:


Purpose of the Script

Import ImagesPurpose of the script in Greek

The script [Import Pictures from folder and subfolders, Retail Price and Product ID] performs the following tasks:

  1. Matches image file paths in a worksheet with corresponding data (Retail Price and Product ID) from a “Price List” worksheet.
  2. Retrieves and displays additional file properties:
    • Date Modified: The date the file was last changed.
    • Size in KB: The size of the file, converted from bytes to kilobytes.
  3. Populates the retrieved data into specific columns in the main worksheet.

YouTube link: https://youtu.be/Qy_B138Ez28


Step-by-Step Explanation

  1. Identify the Worksheets:
    • The script assumes there is a worksheet named “Price List” that contains a table of file paths and their associated data (Retail Price and Product ID).
    • The main worksheet (e.g., “Sheet1”) contains file paths in column I, where additional data will be populated.
  2. Load Data into a Dictionary:
    • The script reads the data from the “Price List” worksheet and stores it in a dictionary for quick lookup.
    • Each key in the dictionary is a file path, and its value is an array containing the Retail Price and Product ID.
  3. Iterate Over File Paths:
    • The script loops through all file paths in column I of the main worksheet.
    • For each file path:
      • It checks if the file exists on the system.
      • It retrieves the file’s Date Modified and Size in KB using the FileSystemObject.
  4. Populate the Worksheet:
    • If a file path matches an entry in the dictionary:
      • The script populates the corresponding Retail Price (column J) and Product ID (column K).
    • It also populates:
      • Date Modified in column L.
      • File Size in KB in column M.
    • All populated data is centered both horizontally and vertically for better readability.
  5. Handle Missing Data Gracefully:
    • If a file path does not exist or cannot be matched, the script leaves the corresponding cells blank.

Columns Populated by the Script

Column Description
J Retail Price of the file (from the Price List sheet).
K Product ID of the file (from the Price List sheet).
L Date the file was last modified.
M Size of the file in KB, rounded to 2 decimal places.

Practical Use Case

This script is ideal for:

  • Managing and updating an inventory of images or files.
  • Automatically retrieving and displaying file properties.
  • Ensuring data accuracy by matching files with a reference list.

What Makes This Script Useful

  1. Automation:
    • Eliminates manual data entry for file properties and matching with a reference list.
  2. Dynamic Matching:
    • Quickly finds and retrieves matching data from the “Price List” sheet using a dictionary for fast lookups.
  3. Enhanced File Insights:
    • Extracts and displays useful metadata like file size and last modification date.

How to Run This Script

  1. Ensure the “Price List” worksheet contains a table with the following columns:
    • Column A: Full file paths.
    • Column B: Retail Price.
    • Column C: Product ID.
  2. In the main worksheet:
    • Add file paths to column I.
    • Leave columns J, K, L, and M blank for the script to populate.
  3. Run the macro to populate the data.

This script simplifies file management by integrating data retrieval, matching, and display into one automated process. Let me know if you need further clarification or enhancements!

Code Explanation:

This VBA code [Import Pictures from folder and subfolders] imports images from a selected folder (and its subfolders) into Excel, starting from a specific row in Sheet1. It positions the images in column G, lists their full file paths in column I, and applies formatting such as text wrapping and column/row dimensions.

Example

folder1

folder1subfolder1subfolder1Import ImagesView VBA macro code Sub InsertImagesInColumnG_Ver2() and Sub InsertImagesFromFolder for button [Import Pictures from folder and subfolders] – Filename: import_images.xlsm
Import Images

Sub InsertImagesInColumnG_Ver2

This is the main procedure that sets up the worksheet, deletes existing images, manages headers, and calls the recursive function ImportImagesFromFolder.

  1. Initialize Worksheet:
    • The worksheet where images will be inserted is set (Sheet1).
    • Deletes all existing images in the worksheet except for shapes with rounded corners:
      For Each picture In ws.Shapes
          If picture.AutoShapeType <> msoShapeRoundedRectangle Then
              picture.Delete
          End If
      Next picture
      
  2. Reset and Style Columns:
    • Clears column I (filenames) and sets headers:
      ws.Cells(4, 7).Value = "Image"
      ws.Cells(4, 9).Value = "Filename"
      ws.Rows(4).Font.Bold = True
      
    • Adjusts column widths:
      • Column G (for images): Width set to 12.14.
      • Column I (for file paths): Width set to 50  with text wrapping enabled:
        ws.Columns("I").ColumnWidth = 50 
        ws.Columns("I").WrapText = True
        
  3. Prompt for Folder Selection:
    • Opens a dialog box for the user to select a folder containing images:
      Set fd = Application.FileDialog(msoFileDialogFolderPicker)
      If .Show = -1 Then
          folderPath = .SelectedItems(1)
      Else
          MsgBox "No folder selected. Operation canceled."
          Exit Sub
      End If
      
  4. Recursive Image Import:
    • Calls the ImportImagesFromFolder subroutine to handle:
      • Importing images from the selected folder.
      • Processing subfolders recursively.
  5. Post-Processing:
    • Adjusts the row height for the first imported image to 66:
      If firstImageRow > 0 Then
          ws.Rows(firstImageRow).RowHeight = 66
      End If
      
    • Moves the first image down by 0.5 points for better alignment:
      If Not firstPicture Is Nothing Then
          firstPicture.Top = firstPicture.Top + 0.5
      End If
      
  6. Completion Notification:
    • Informs the user that the task is complete.

Sub ImportImagesFromFolder

This subroutine handles the actual image insertion and recursively processes subfolders.

  1. Set Up File System Objects:
    • Uses the FileSystemObject to handle folder traversal:
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set folder = fso.GetFolder(folderPath)
      Set subFolders = folder.subFolders
      
  2. Import Images:
    • Loops through all files in the current folder. For each image file:
      • Inserts it into column G as a shape:
        Set picture = ws.Shapes.AddPicture(folderPath & fileName, _
            msoFalse, msoCTrue, _
            ws.Cells(imgRow, 7).Left, _
            topPosition, _
            64, 64)
        
      • Moves the image slightly to the right for alignment:
        picture.Left = picture.Left + 1.2
        
      • Tracks the first image for special formatting.
  3. Record Full File Path:
    • Writes the full path of each image in column I and centers the text vertically:
      ws.Cells(imgRow, 9).Value = folderPath & fileName
      ws.Cells(imgRow, 9).VerticalAlignment = xlCenter
      
  4. Adjust Row Height and Position:
    • Sets the row height to 64.5 for every imported image and moves the starting position for the next image:
      ws.Rows(imgRow).RowHeight = 64.5
      topPosition = topPosition + 64 + 0.5
      imgRow = imgRow + 1
      
  5. Process Subfolders:
    • Recursively calls itself for each subfolder:
      For Each subFolder In subFolders
          Call ImportImagesFromFolder(ws, subFolder.Path & "\", imgRow, topPosition, firstImageRow, firstPicture)
      Next subFolder
      

Key Features:

  1. Recursive Subfolder Handling:
    • Automatically searches subfolders for images, ensuring no files are missed.
  2. Full File Path in Column I:
    • Displays the complete file path, making it easy to locate the source of each image.
  3. Custom Formatting:
    • Headers, column widths, and text wrapping improve readability.
    • Row heights and image alignment ensure a visually appealing layout.
  4. Dynamic Deletion:
    • Removes existing non-rounded-corner shapes before inserting new images.
  5. User-Friendly:
    • Prompts for a folder and provides completion feedback via a message box.

This VBA macro (Version 2) is ideal for dynamically importing, organizing, and displaying images and their source paths from a selected folder and its subfolders. Let me know if you’d like to enhance it further!

Version 1

Import images

View VBA macro code Sub InsertImagesInColumnG() for button [Import Pictures from folder]

Version 2

Import Images

Import images

View VBA macro code Sub InsertImagesInColumnG_Ver2() and Sub InsertImagesFromFolder for button [Import Pictures from folder and subfolders]

Version 3

Import Images

View VBA macro code Sub InsertImagesInColumnG_Ver3() for button [Import Pictures from folder and subfolders, Retail Price and Product ID] upon request. Send your request to email: info@mindstorm.gr

Import Images

 

Views: 15

Comments are closed.

Pin It