VBA & Macro Problems

Excel VBA Macro to Download PDF Files from URLs

Phi Hung Vo Sep 28, 2026 870 views

Question details

The user wants an Excel VBA macro to automate downloading PDF files from a list of supplier URLs, save them with specific names, and accurately record the download status.

Excel VBA Macro to Download PDF Files from a List of URLs

Product: Excel
Device & OS: not provided
Scenario: Automating bulk PDF file downloads based on URLs in a spreadsheet, while correctly identifying and skipping placeholder PDFs returned for non-existent products.
Observed behavior: The user needs the macro to distinguish between valid product PDFs and 1KB placeholder PDFs, write the results (Downloaded, Not Found, Invalid PDF) into column C, and use the filename from column B.

Before you start

Ensure you have Developer options enabled in your spreadsheet program and that your file is saved as a Macro-Enabled Workbook (.xlsm) before writing or pasting VBA code.

Solution 1Recommended

Use Microsoft XMLHTTP and ADODB.Stream via VBA

This method uses native Windows libraries to fetch URL content, check the response size to filter out placeholder PDFs, and save valid binary data locally.

By utilizing the XMLHTTP object, VBA can send a request to the server and read the HTTP status and response size. ADODB.Stream is then used to convert the downloaded binary data into a physical PDF file on your hard drive.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank module for your code.

Initialize XMLHTTP and ADODB objects

Write your subroutine and use CreateObject("MSXML2.XMLHTTP") to handle the web request, and CreateObject("ADODB.Stream") to handle the file saving.

Loop through the URLs

Create a loop that reads the URL from column A and the target filename from column B for each row in your dataset.

Send the GET request and validate size

Send a GET request to the URL. Before saving, check the response length (e.g., LenB(xmlHttp.responseBody)). If it is around 1KB, it is a placeholder PDF.

Save the file and record the status

If the file is valid, use the ADODB.Stream to save the response body as a .pdf using the filename from column B. Finally, write 'Downloaded', 'Not Found', or 'Invalid PDF' into column C based on the conditions.

Use Microsoft XMLHTTP and ADODB.Stream via VBA

Handling Invalid Products: Checking the HTTP status code (200 OK) and the byte size of the responseBody ensures that tiny 1KB placeholder files are skipped automatically without interrupting the loop.

Automate Data Tasks Seamlessly in WPS Spreadsheet

WPS Office provides robust support for VBA macros in its Spreadsheet application, allowing you to run complex automation scripts like bulk downloading PDFs without needing Microsoft Excel.

1. Install WPS Office: Download and install the free WPS Office suite on your computer.
2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the list of URLs.
3. Access the Developer Tab: Navigate to the Developer tab on the ribbon and click 'VBA Editor' to access your macro code.
4. Run the Macro: Paste your XMLHTTP download script and press 'Run' to execute the batch download.

High compatibility with Microsoft Excel VBA code and .xlsm formatsLightweight installation with fast execution of automated scriptsFree and intuitive macro editor interface for developers

microsoft office alternative - wps office

Frequently Asked Questions

How do I identify a 1KB placeholder PDF in my VBA code?

You can check the byte size of the downloaded data before saving it. By measuring the length of the `responseBody` from the XMLHTTP request, you can add an `If` statement to skip the save process if the size is below a certain threshold (e.g., 2000 bytes).

Why do I get an error when using ADODB.Stream?

This commonly occurs if the destination folder path does not exist, if the filename contains illegal characters, or if you do not have write permissions to the directory. Ensure the path extracted from column B is complete and valid.

Does this macro work on a Mac?

No, the MSXML2.XMLHTTP and ADODB.Stream libraries rely on Windows-specific ActiveX components. To achieve this on macOS, you would need to use Mac-specific commands like `MacScript` to invoke cURL commands via AppleScript.

Excel & Data Problems