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
Purpose of the script in Greek
The script [Import Pictures from folder and subfolders, Retail Price and Product ID] performs the following tasks:
- Matches image file paths in a worksheet with corresponding data (Retail Price and Product ID) from a “Price List” worksheet.
- 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.
- Populates the retrieved data into specific columns in the main worksheet.
YouTube link: https://youtu.be/Qy_B138Ez28
Step-by-Step Explanation
- 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.
- 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.
- 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
.
- 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.
- If a file path matches an entry in the dictionary:
- 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
- Automation:
- Eliminates manual data entry for file properties and matching with a reference list.
- Dynamic Matching:
- Quickly finds and retrieves matching data from the “Price List” sheet using a dictionary for fast lookups.
- Enhanced File Insights:
- Extracts and displays useful metadata like file size and last modification date.
How to Run This Script
- 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.
- In the main worksheet:
- Add file paths to column I.
- Leave columns J, K, L, and M blank for the script to populate.
- 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
subfolder1
—
View VBA macro code Sub InsertImagesInColumnG_Ver2() and Sub InsertImagesFromFolder for button [Import Pictures from folder and subfolders] – Filename: import_images.xlsm
Sub InsertImagesInColumnG_Ver2
This is the main procedure that sets up the worksheet, deletes existing images, manages headers, and calls the recursive function ImportImagesFromFolder
.
- 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
- The worksheet where images will be inserted is set (
- 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
- Column G (for images): Width set to
- Clears column I (filenames) and sets headers:
- 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
- Opens a dialog box for the user to select a folder containing images:
- Recursive Image Import:
- Calls the
ImportImagesFromFolder
subroutine to handle:- Importing images from the selected folder.
- Processing subfolders recursively.
- Calls the
- 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
- Adjusts the row height for the first imported image to
- Completion Notification:
- Informs the user that the task is complete.
Sub ImportImagesFromFolder
This subroutine handles the actual image insertion and recursively processes subfolders.
- 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
- Uses the
- 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.
- Inserts it into column G as a shape:
- Loops through all files in the current folder. For each image file:
- 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
- Writes the full path of each image in column I and centers the text vertically:
- 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
- Sets the row height to
- Process Subfolders:
- Recursively calls itself for each subfolder:
For Each subFolder In subFolders Call ImportImagesFromFolder(ws, subFolder.Path & "\", imgRow, topPosition, firstImageRow, firstPicture) Next subFolder
- Recursively calls itself for each subfolder:
Key Features:
- Recursive Subfolder Handling:
- Automatically searches subfolders for images, ensuring no files are missed.
- Full File Path in Column I:
- Displays the complete file path, making it easy to locate the source of each image.
- Custom Formatting:
- Headers, column widths, and text wrapping improve readability.
- Row heights and image alignment ensure a visually appealing layout.
- Dynamic Deletion:
- Removes existing non-rounded-corner shapes before inserting new images.
- 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
View VBA macro code Sub InsertImagesInColumnG() for button [Import Pictures from folder]
Version 2
View VBA macro code Sub InsertImagesInColumnG_Ver2() and Sub InsertImagesFromFolder for button [Import Pictures from folder and subfolders]
Version 3
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
Comments are closed.