VBA & Macro Problems

How to Add a Modified-Date Column to Multiple CSV Files using VBA

Adam Davis Nov 11, 2026 868 views

Question details

The user needs a method to batch process thousands of CSV files, checking each for a modified-date column and appending the file's last modified timestamp if the column is missing.

How to Add a Modified-Date Column to Thousands of CSV Files Using VBA

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Processing a large directory of CSV files to append metadata selectively based on existing headers without manually opening each file.
Observed behavior: The user wants to automate opening, inspecting, updating, and saving thousands of CSV files while properly controlling memory usage.

Before you start

Always create a secure backup copy of your entire folder containing the CSV files before running any batch-processing scripts to prevent irreversible data loss in case of logic errors.

Solution 1Recommended

Use a VBA Macro to Batch Update CSV Files

A customized VBA script can loop through all CSV files in a specific folder, read the header row, and append the modified date using built-in file system objects.

VBA provides a FileSystemObject that enables seamless interaction with Windows folders and files. By utilizing an automated loop, the script can process each file sequentially. Reading the file as a text stream rather than opening it in the spreadsheet grid is often faster and uses significantly less memory.

Open the VBA Editor

Launch your spreadsheet application and press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank canvas for your code.

Write the Loop and File Validation

Write a script utilizing 'Scripting.FileSystemObject' to define the target folder path and loop through each '.csv' file. Use the 'FileDateTime' function to retrieve the last modified timestamp for the current file.

Inspect and Update the Header

Within the loop, use the 'Open' statement to read the first line. If the 'Modified-Date' string is absent, rewrite the file contents by appending the timestamp to the end of each line string.

Close the File Stream

Ensure you close the file immediately after rewriting to prevent memory leaks, then allow the loop to proceed to the next file.

Use a VBA Macro to Batch Update CSV Files

Memory Management: Ensure your VBA code explicitly closes each text stream or workbook instance after processing. Failing to close files inside a loop of thousands of items will cause the application to run out of system memory.

Solution 2

Use a PowerShell Script for Native Windows Processing

For handling thousands of CSV files without opening a spreadsheet application, a native Windows PowerShell script is highly efficient.

Process Data Effectively

Batch Process CSVs with WPS Spreadsheet

WPS Spreadsheet offers robust VBA and Macro support, allowing you to easily run custom scripts to automate repetitive tasks like updating thousands of CSV files. It is lightweight, highly compatible with Excel formats, and designed for efficient data handling.

1. Enable Developer Tools: Open WPS Spreadsheet, go to settings, and ensure the Developer tools are enabled in your ribbon.
2. Open the VB Editor: Click on the 'Developer' tab and select 'VB Editor' to open the scripting environment.
3. Paste Your Script: Insert a new module and paste your CSV batch-processing script.
4. Run the Macro: Click 'Run' to execute the macro and automatically process your folder of CSV files.

Seamless compatibility with Microsoft Excel VBA syntaxLightweight design processes large datasets efficientlyFree to download with comprehensive spreadsheet capabilitiesSupports seamless importing and exporting of CSV formats

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA script crash when processing thousands of files?

This usually happens due to memory leaks. Ensure your VBA script uses 'ActiveWorkbook.Close SaveChanges:=True' or closes the file stream ('Close #1') correctly after updating each CSV file to release the memory.

Can I preserve the original CSV delimiter when saving via VBA?

Yes. If you are using 'Workbooks.OpenText', make sure to specify 'Local:=True' so Excel uses your system's default delimiter. Alternatively, using 'FileSystemObject' to read and write the CSV as plain text prevents the application from automatically changing the delimiter.

How do I fetch a file's last modified date in VBA?

You can use the built-in 'FileDateTime("C:\path\to\file.csv")' function, which returns a string containing the date and time the file was created or last modified.

Is PowerShell faster than VBA for this specific task?

PowerShell can often be faster for plain text processing because it bypasses the need to render the graphical interface or calculation engine of a spreadsheet application. However, VBA is highly convenient if you are already comfortable working within the spreadsheet ecosystem.

Excel & Data Problems