How to Batch-Edit Pictures in Excel with VBA
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.

- 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 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.
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.
In your Excel workbook, press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
Click on 'Insert' in the top menu bar and select 'Module' to create a new blank code window.
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
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.

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. Open the Developer Tab: Launch WPS Spreadsheet, navigate to the top ribbon, and click on the 'Developer' tab to access advanced tools.
- 2. Access the VBA Editor: Click the 'VBA Editor' button to open the coding environment where you can insert new modules.
- 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.

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.




