VBA & Macro Problems

How to Use VBA to Auto-Insert Images and Print Rows in Excel

Chanuka Geekiyanage Nov 2, 2026 872 views

Question details

The user wants an automated VBA workflow to copy a row of data to a print workbook, insert a corresponding image based on its filename, print the result, and clear the data to repeat for the next row without manual intervention.

How to Use Excel VBA to Insert Images, Print Rows, and Repeat Automatically

Product: Excel
Device & OS: not provided
Scenario: Batch printing records with dynamic images from a dataset.
Observed behavior: The goal is to bypass manual image-selection dialogs and fully automate the cycle of copying data, inserting images, printing, and clearing the target sheet.

Before you start

Ensure all your target image files are stored in a single, accessible folder and that your Excel dataset contains the exact filenames or full file paths. Save your file as an Excel Macro-Enabled Workbook (.xlsm) before adding the code.

Solution 1Recommended

Create a VBA Macro to Automate Image Insertion and Printing

Write a VBA loop that reads filenames, inserts the images into a designated print sheet, prints the sheet, and clears it for the next row.

This solution utilizes a standard VBA 'For' or 'Do While' loop to iterate through your dataset. For each row, it copies the data to a 'Print' sheet, uses the 'Shapes.AddPicture' method to place the image, calls 'PrintOut', and then deletes the image so the layout is ready for the next iteration.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications window, then click Insert > Module to create a new script area.

Define the Loop and Data Transfer

Write a loop that goes through your main dataset row by row. Inside the loop, copy the required text data (like names or IDs) to specific cells in your target 'Print' worksheet.

Insert the Image Dynamically

Read the file path from the current row. Use the 'ActiveSheet.Shapes.AddPicture' method, specifying the file path, link settings, and positioning coordinates to insert the image precisely on the print sheet.

Print the Worksheet

Add the 'ActiveSheet.PrintOut' command to send the completed layout to your default printer.

Clear Data and Shapes

Before the loop loops back, clear the copied text data and delete the inserted image shape (e.g., using 'ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Delete') to ensure the next printout does not overlap.

Create a VBA Macro to Automate Image Insertion and Printing

Verify File Paths: Make sure the file paths constructed in your VBA code match the actual locations on your computer to avoid 'File Not Found' runtime errors.

Automate Batch Printing and Image Insertion with WPS Spreadsheet

WPS Office provides robust VBA and Macro support, allowing you to easily run automated scripts to insert images, process data rows, and print documents in batches without manual repetitive work.

1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the data and VBA code.
2. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon and click on 'Macros' to view your available scripts.
3. Run the Batch Print Script: Select your automated image insertion and printing macro, then click 'Run'.
4. Process Automatically: Allow WPS Spreadsheet to seamlessly process the row data, insert images, and send jobs to your printer.

Fully compatible with Microsoft Excel VBA macros (.xlsm format)Seamlessly batch process and print records with dynamic image insertionLightweight application with high performance for large automated datasetsFamiliar user interface requiring no relearning for macro execution

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro fail with an error when trying to insert the image?

This usually happens if the file path is incorrect or the image file has been renamed or moved. Verify that the file path text in your worksheet exactly matches the file's current location on your drive.

Can I adjust the size of the inserted images using VBA?

Yes, when using the 'AddPicture' method in VBA, you can specify the width and height parameters (usually the last two arguments) to ensure every image fits perfectly into your designated print layout cell.

How do I stop the macro if it prints too many pages or gets caught in an endless loop?

You can forcefully stop a running VBA macro by pressing 'Ctrl + Break' (or 'Esc' repeatedly) on your keyboard. This will pause the execution and allow you to debug the code.

Excel & Data Problems