VBA & Macro Problems

How to Batch-Edit Pictures in Excel with VBA

Rana GarciaRana Garcia Oct 20, 2026 871 views

Question details

The user needs to batch-edit pictures using Excel VBA by pulling image paths and text from specific columns, applying a red text overlay to the images, and exporting them back to their original file paths.

How to Batch-Edit Pictures in Excel Using VBA Macros
Product
Excel
Device & OS
not provided
Scenario
Automating the process of watermarking or adding text annotations to multiple images using data stored in an Excel spreadsheet.
Observed behavior
The user wants to successfully run a VBA macro that reads file paths from column B, adds combined text from columns A and C in red to the upper-right corner of the image, and overwrites the original files.
Before you start

Before running the macro, ensure you create a backup copy of your original images in a separate folder, as this VBA script is designed to overwrite the source files with the newly edited versions.

Solution 1Recommended

Use a VBA Macro to Batch-Add Text Overlays to Pictures

Run a custom VBA script to automatically insert images based on file paths, overlay text from specified cells, and export the new images.

This macro loops through selected cells in column A. It assumes the image file path is located in column B and the appended text is in column C.

The script will group the inserted image and the text box, copy the group to a temporary chart object, and export it as a PNG or JPG based on the original extension, replacing the original file.

1
Open the VBA Editor

In your Excel workbook, press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to create a new blank code window.

3
Paste the Macro Code

Copy and paste the following code into the module window: Sub AddTextToPictures() Dim cell As Range Dim filePath As String, caption As String Dim ws As Worksheet Dim picture As Shape, textBox As Shape, groupedShape As Shape Dim chartObj As ChartObject Dim filterName As String Set ws = ActiveSheet For Each cell In Selection.Cells filePath = Trim(CStr(cell.Offset(0, 1).Value)) If Len(filePath) > 0 And Len(Dir(filePath)) > 0 Then Set picture = ws.Shapes.AddPicture(filePath, msoFalse, msoTrue, 10, 10, -1, -1) picture.LockAspectRatio = msoTrue caption = CStr(cell.Value) & " - " & CStr(cell.Offset(0, 2).Value) Set textBox = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, picture.Left, picture.Top, picture.Width * 0.45, 30) With textBox .Fill.Visible = msoFalse .Line.Visible = msoFalse .TextFrame2.TextRange.Text = caption .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0) .TextFrame2.TextRange.Font.Size = 14 .TextFrame2.TextRange.Font.Bold = msoTrue .TextFrame2.HorizontalAnchor = msoAnchorRight .TextFrame2.VerticalAnchor = msoAnchorTop .Left = picture.Left + picture.Width - .Width .Top = picture.Top End With Set groupedShape = ws.Shapes.Range(Array(picture.Name, textBox.Name)).Group Set chartObj = ws.ChartObjects.Add(0, 0, groupedShape.Width, groupedShape.Height) groupedShape.Copy chartObj.Chart.Paste chartObj.Chart.ChartArea.Format.Fill.Visible = msoFalse filterName = IIf(LCase$(Right$(filePath, 4)) = ".png", "PNG", "JPG") chartObj.Chart.Export Filename:=filePath, FilterName:=filterName chartObj.Delete groupedShape.Delete End If Next cell End Sub

4
Run the Macro

Close the VBA editor. In your worksheet, select the target cells in column A. Press Alt + F8, select 'AddTextToPictures' from the Macro dialog box, and click 'Run' to process the images.

Use a VBA Macro to Batch-Add Text Overlays to Pictures
Adjusting Cell Offsets: If your worksheet uses a different column layout, adjust the 'cell.Offset(0, 1)' and 'cell.Offset(0, 2)' values in the code to point to the correct columns for your file paths and additional text.
Advanced Spreadsheets

Batch Process Macros Easily with WPS Spreadsheet

WPS Office provides robust native support for VBA and macros, allowing you to run complex automation scripts like batch-editing pictures directly within your spreadsheet with ease.

  1. 1. Open the Developer Tab: Launch WPS Spreadsheet, navigate to the top ribbon, and click on the 'Developer' tab to access advanced tools.
  2. 2. Access the VBA Editor: Click the 'VBA Editor' button to open the coding environment where you can insert new modules.
  3. 3. Paste and Execute: Insert a new module, paste your picture-editing macro script, select your data in the spreadsheet, and run the macro via the Macros button.
Fully compatible with Microsoft Excel VBA macros and scripts.Lightweight architecture ensures complex scripts run smoothly without crashing.Includes a free built-in VBA editor and comprehensive developer tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why is the macro failing to export the edited picture?

If the macro stops or fails at the export stage, ensure that the file paths in column B are absolutely correct, include the correct file extension (.png or .jpg), and verify that the destination folder is not set to read-only.

Can I change the color of the text overlay in the macro?

Yes, you can modify the text color by changing the RGB values in the macro line `.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)`. For example, change it to RGB(0, 0, 0) for black or RGB(255, 255, 255) for white.

How do I enable the Developer tab to run this VBA code?

To enable the Developer tab, go to File > Options > Customize Ribbon. In the right pane, locate the 'Developer' checkbox, check it, and click OK. The tab will now appear on your main Excel ribbon.