How to Generate Catalog Pages from Spreadsheet Data and Product Photos
Product and merchandising teams can turn a structured Excel sheet and a folder of product photos into tidy catalog pages without manual copy‑pasting. The method runs locally on a Windows PC, keeps files on your machine, and produces both Word and PDF outputs in one batch.
From Excel to PDF in minutes
Insert product photos automatically
Quick answer
The fastest way to create catalog pages is to let a VBA macro read each row of a spreadsheet, open a Word template, replace text placeholders, insert the matching product photo, and then save the result as a Word file and a PDF. All of these steps happen on the user’s computer, so no files leave the organisation.
The macro takes the product name, SKU and image filename from the sheet, finds the corresponding picture on disk, drops it into a bookmark, and writes out both a DOCX and a PDF. One run processes the whole list, eliminating repetitive manual work.
Why this matters
Catalog creation is a repetitive, error‑prone chore when it relies on manual copy‑paste and hand‑inserting images. Automating the process protects brand consistency, speeds time‑to‑market, and frees the merchandising team to focus on content quality rather than file fiddling.
Reduce manual effort
When each spreadsheet row maps to a finished page, a single macro can generate dozens or hundreds of documents with a click. The team no longer has to open Word for every product, type details, or locate images one by one, which cuts labour hours dramatically.
Maintain brand consistency
All visual elements—logos, signatures, product photos—are inserted from a controlled folder and placed at predefined bookmarks. This guarantees that every catalog page follows the exact layout and resolution rules, preventing drift that often occurs with ad‑hoc manual edits.
What goes wrong
A manual approach quickly shows its limits: missing images, mismatched filenames, and inconsistent formatting become common sources of frustration.
Manual copy‑paste workflow
Team members copy values from Excel into Word, hunt for the right photo on a shared drive, resize it manually, and then save the file. Errors such as wrong SKUs, missing pictures, or inconsistent font styles are frequent, and each mistake requires time‑consuming rework.
Automated VBA workflow
A single VBA routine reads each row, verifies that the referenced image exists, inserts it at a bookmark, applies the correct DPI handling, and saves both DOCX and PDF. The macro aborts with a clear message if a photo is missing, ensuring no incomplete catalog pages slip through.
Automation eliminates the guesswork and keeps the output reliable.
What the workflow looks like
The end‑to‑end workflow stitches together three simple assets—a spreadsheet, a Word template, and a photos folder—then runs a macro that ties them together in a repeatable batch.
Prepare the source sheet
Create an Excel worksheet where each row represents one catalog page. Include columns for product name, SKU, and the exact image filename (without path). Keep the header row and make sure the data types are consistent.
Design the Word template
Insert text placeholders like <
Collect product photos
Place every product image in a single folder, using the filenames referenced in the Excel column. JPG works for standard photos; PNG is required for transparent logos or stamps. Ensure the folder is accessible from the macro’s working directory.
Run the VBA macro
Open the Excel workbook, press ALT + F8, and run the GenerateCatalog macro. The code opens the template for each row, swaps the placeholders, inserts the matching picture, saves a DOCX, and then exports a PDF to a sibling output folder.
Verify and distribute
After the macro finishes, browse the Word and PDF output folders. Spot‑check a few files for correct data, proper image placement and resolution, then move the finished catalog pages to the publishing system or share them with the sales team.
A visual example
Simple visual illustration.
AI-generated illustration for article.
A grounded VBA example
The macro below demonstrates a grounded VBA solution that ties Excel data, a Word template and a photo library together without any external tools.
It loops through every data row, opens the template, replaces text placeholders, inserts the product picture at a bookmark, saves both DOCX and PDF versions, and reports any missing images.
Sub GenerateCatalog()
Dim xl As Workbook, ws As Worksheet
Dim wdApp As Object, wdDoc As Object
Dim tmplPath As String, outFolder As String, imgFolder As String
Dim lastRow As Long, i As Long
Dim prodName As String, sku As String, imgName As String
Dim imgPath As String
Set xl = ThisWorkbook
Set ws = xl.Sheets("CatalogData")
tmplPath = xl.Path & "\Template.docx"
outFolder = xl.Path & "\Output\Word"
imgFolder = xl.Path & "\Photos"
' Ensure output folder exists
If Dir(outFolder, vbDirectory) = "" Then MkDir outFolder
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow ' assume header row
prodName = ws.Cells(i, "A").Value
sku = ws.Cells(i, "B").Value
imgName = ws.Cells(i, "C").Value ' filename without extension
Set wdDoc = wdApp.Documents.Open(tmplPath, ReadOnly:=False)
' Replace placeholders in the document
With wdDoc.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "<<ProductName>>"
.Replacement.Text = prodName
.Execute Replace:=2, Forward:=True, Wrap:=1
.Text = "<<SKU>>"
.Replacement.Text = sku
.Execute Replace:=2, Forward:=True, Wrap:=1
End With
' Insert product photo
imgPath = imgFolder & "\" & imgName & ".jpg"
If Dir(imgPath) <> "" Then
wdDoc.Bookmarks("photo_product").Range.InlineShapes.AddPicture _
FileName:=imgPath, LinkToFile:=False, SaveWithDocument:=True
End If
' Save as DOCX
Dim docFile As String
docFile = outFolder & "\" & sku & ".docx"
wdDoc.SaveAs2 docFile, 16 ' wdFormatXMLDocument
' Export PDF to sibling folder
Dim pdfFolder As String
pdfFolder = xl.Path & "\Output\PDF"
If Dir(pdfFolder, vbDirectory) = "" Then MkDir pdfFolder
wdDoc.ExportAsFixedFormat OutputFileName:=pdfFolder & "\" & sku & ".pdf", _
ExportFormat:=17 ' wdExportFormatPDF
wdDoc.Close SaveChanges:=False
Next i
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
MsgBox "Catalog generation complete.", vbInformation
End SubAdjust the placeholder names or folder paths to match your own project structure before running.
Where VBA starts to strain
While VBA handles most catalog‑generation scenarios well, there are practical limits you should be aware of, especially when scaling to large product catalogs or integrating more complex layout logic.
Performance with large image sets
If the photo folder contains thousands of high‑resolution images, opening and inserting each picture can become slow and consume considerable memory. Consider pre‑resizing images to the recommended 150 DPI for standard photos, or batch the run in smaller chunks.
Error‑handling boundaries
The macro aborts only when a referenced image file is missing. It does not automatically correct mismatched column headers or malformed Excel data, so a quick data‑validation step before execution is advisable.
Maintainability and debugging
As the macro grows (e.g., adding conditional formatting or multi‑page layouts), debugging becomes harder. Keep the code modular by extracting reusable routines (e.g., InsertImage, ReplacePlaceholders) and add verbose logging to a text file. Version‑control the VBA module in a shared repository to track changes and roll back if needed.
A calmer way to standardize the workflow
DocxForge Pro builds on this manual VBA pattern and adds a purpose‑built batch engine that removes the need to write or maintain code.
For repeatable business documents such as reports contracts certificates letters and packs DocxForge Pro can act as the local layer between spreadsheet data Word templates and final output. It supports selected photo-folder workflows and special tags such as photo_logo photo_signature and photo_stamp.
This fits teams that produce repeatable reports contracts certificates forms letters or document packs from structured data. This is especially useful when image handling is part of the document workflow not a separate manual step. The article’s VBA example shows the spreadsheet-side automation while DocxForge Pro fits as the document-generation layer outside the code example itself.
Start Free 7-Day TrialDocxForge Pro batch engine
Runs the same Excel‑to‑Word‑to‑PDF pipeline using a graphical configuration, handles image caching, and creates both DOCX and PDF outputs without writing VBA. It also reports missing files in a clear summary.
Built‑in image handling
The product stages images to the correct DPI, supports special tags like photo_logo and photo_stamp, and stores a local cache so repeated runs are faster and more reliable.
Frequently asked questions
Common questions about turning spreadsheet rows and photos into catalog pages
Is this workflow suitable for generating catalog pages from spreadsheet data and product photos?
Yes. The pattern assumes each spreadsheet row contains the data needed for one page and that a matching photo exists in a designated folder. As long as the template uses bookmarks or placeholders, the macro can produce a complete DOCX and PDF for every record.
What source data has to stay consistent before generation starts?
Column headers must remain stable (e.g., ProductName, SKU, PhotoFile). The photo filenames referenced in the sheet should exactly match the files on disk, including case sensitivity on Windows‑based shares. Keeping a single source of truth for image resolution (150 DPI standard) also avoids unexpected scaling.
How do I adapt the template without breaking the workflow?
Add or rename placeholders only in pairs: update the Excel column heading, modify the Find‑Replace strings in the macro, and adjust the bookmark name if you change image locations. Running the macro on a single test row after each change confirms that the mapping still works.
Can this process scale across many records and templates?
The VBA macro can handle hundreds of rows, but very large batches may benefit from splitting the work into smaller groups or using DocxForge Pro, which provides a dedicated batch runner and better memory management for massive image collections.
Topics and Tags
Browse related topic clusters and workflow tags connected to this article.
Continue Reading
Explore more articles related to this workflow, problem, or document automation topic.
How to Build Offline Insurance Claim Document Packs
A step‑by‑step guide for insurance operations teams to build offline claim document packs using Excel, Word, and a safe VBA macro.
Read articleHow to Create Equipment Checklists with Photos and PDF Output
Guide for operations teams to automate equipment inspection checklists that embed field photos and produce PDF files using local Excel and Word automation.
Read articleHow to Create Photo-Based Property Condition Reports
Create photo-based property condition reports
Read articleHow to Automate Incident Reports with Photos and Signatures
A practical guide for safety and operations teams to automate incident report creation, embedding field photos and employee signatures using a local Excel‑to‑Word‑to‑PDF workflow.
Read article