VBA & Macro Problems

How to Use a VBA Macro to Find Shipping PDFs and Update Excel Rows

Maira Mehtab Sep 20, 2026 871 views

Question details

The user needs to implement a VBA macro that searches specific network directories for PDF files matching values in column U, and then writes the corresponding shipping folder name to column P.

Product: Excel
Device & OS: not provided
Scenario: Automating the process of matching shipping documents stored in network subfolders to corresponding records in a spreadsheet.
Observed behavior: The macro searches the specified directory and updates rows until it hits an empty cell in column U. However, errors or zero results may occur if the modules are imported incorrectly or if folder names do not match perfectly.

Before you start

Ensure you have the required FileSearch class module downloaded to your local machine, and verify that you have active network access to the S:\WAREHOUSE FILES\ETA directory before proceeding.

Solution 1Recommended

Import Modules and Execute the VBA Macro

Use this primary method to install the provided VBA code into your workbook to automate the PDF search and row update process.

To run this specific automation successfully, the code is split into a class module (for the file search logic) and a standard module (for the execution logic).

Open the VBA Editor

Open your workbook, navigate to the Developer tab on the ribbon, and click 'Visual Basic' (or press ALT + F11) to open the VBA editor.

Import the Class Module

In the VBA editor, right-click on your VBAProject in the left panel, select 'Import File', and choose the downloaded FileSearch class module file.

Insert the Standard Macro

Right-click the VBAProject again, select 'Insert' > 'Module', and paste the remaining macro code into this new standard module window.

Run the Automation

Place your cursor inside the main macro sub-procedure and click the 'Run' button (the green play icon) on the toolbar, or press F5, to start searching for PDFs and updating column P.

Automatic Stop Condition: The macro is designed to process the rows sequentially and will safely stop execution as soon as it detects the first empty row in column U.

Solution 2

Troubleshoot Zero Results or Code Errors

Use these checks if running the macro returns zero results, fails to update column P, or highlights lines of code in red.

Advanced Automation

Automate Workflows with Macros in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to seamlessly run custom scripts to search network folders, match PDF filenames, and update your worksheets automatically.

1. Open Your Workbook: Launch WPS Office, select Spreadsheet, and open the .xlsm file containing your shipping records.
2. Access the Developer Tools: Click on the 'Developer' tab in the top ribbon to access the automation and macro toolset.
3. Open the VBA Editor: Click the 'VBA Editor' button to import your FileSearch class module and standard macro modules.
4. Run the Script: Execute your file-matching macro directly within WPS to update your folder paths in column P automatically.

Excellent compatibility with Microsoft Excel VBA macros (.xlsm and .xlsb formats)Lightweight architecture for fast execution of complex file search scriptsBuilt-in VBA editor equipped with debugging tools and step-through capabilities

Frequently Asked Questions

Why is my VBA code highlighted in red when I paste it?

Red text in the VBA editor indicates a syntax error. This commonly happens if code is copied improperly, causing a single line of code to break into two separate lines. You can fix this by removing the incorrect line break or re-copying the code.

Why does the macro return zero results even though the PDFs exist?

This usually occurs if the target subfolder is not named exactly 'SHIPPING DOCUMENTS', or if the text in column U contains extra spaces or characters that prevent it from perfectly matching the actual PDF filename.

Will the macro run endlessly if column U has blank spaces?

No. The provided automation is designed to evaluate each row and will deliberately stop processing at the first empty row it encounters in column U.

How do I import the FileSearch class module?

Open the VBA editor, right-click 'VBAProject' in the Project Explorer pane on the left, select 'Import File...', and browse for the downloaded class module file to add it to your project.

Excel & Data Problems