VBA & Macro Problems

Merge CSV Files in WPS Spreadsheets and Add Source File Names

Steve KSteve K Nov 13, 2026 870 views

Question details

The user needs to combine multiple CSV files into one worksheet and use each source file name as the heading for its imported data column.

Merge CSV Files and Add Source File Names in WPS Spreadsheets
Product
WPS Spreadsheets / Microsoft Excel
Device & OS
not provided
Scenario
The user has several CSV files with a shared first column and a second data column in each file. They want to import all second columns under their source file names in a single worksheet.
Observed behavior
The current VBA macro imports data but does not correctly add the source file name as a heading, and the expected file order may differ because Dir returns file-system order rather than creation-date order.
Before you start

Place all CSV files in one folder, confirm they have the same column layout, and make sure the shared first column values match row by row. Enable macros in WPS Spreadsheets or Excel before opening the VBA editor.

Solution 1Recommended

Update the VBA Macro to Import Each CSV File Under Its Source File Name

Use this method when the CSV files share an identical first column and you want a repeatable VBA routine that writes each file name without the .csv extension into row 1.

The macro uses the Dir function to read all .csv files from a folder. It copies the shared first column once, then opens each file and copies its second column beneath the corresponding file name. Dir returns files in file-system name order, not creation-date order, so rename files or sort later if creation order matters.

1
Open the VBA editor

In WPS Spreadsheets or Excel, open your workbook, select the Developer tab, and click Macros or press Alt+F11 to open the VBA editor.

2
Insert a new module

In the VBA editor, click Insert > Module, paste the corrected macro code, and set the folderPath variable to the folder that contains your CSV files.

3
Use the file name as the column heading

In the macro, replace the header assignment with Replace(fileName, ".csv", "") so each source file name is written to row 1 without its extension.

4
Run the merge

Press F5 or click Run > Run Sub/UserForm, return to the spreadsheet, and confirm that each imported second column has the correct source file name heading.

Update the VBA Macro to Import Each CSV File Under Its Source File Name
Expected result: The shared first column is copied once, and each CSV file’s second column is imported under its source file name without the .csv extension.

Merge CSV Files in WPS Spreadsheets with a VBA Macro

WPS Spreadsheets supports VBA macros, so you can automate the CSV merge directly: create a macro that opens each .csv file in a folder, copies the shared first column once, and places each second column under its source file name.

  1. 1. Open WPS Spreadsheets: Download WPS Office from the official site, install it, and open WPS Spreadsheets with a new blank workbook.
  2. 2. Enable the Developer tab: Go to File > Options > Customize Ribbon and enable Developer, or open an existing macro-enabled workbook.
  3. 3. Create the merge macro: Open the VBA editor with Alt+F11, insert a module, and paste the corrected macro that reads .csv files from your chosen folder.
  4. 4. Run the macro: Press F5 to run the macro; it will import the shared first column once and add each source file name as the heading for its second column.
  5. 5. Save the workbook: Save the result as .xlsx or .xlsm to keep the merged data compatible with Microsoft Excel.
Automatically merges multiple CSV files and names each imported column with its source file.Uses a familiar VBA macro workflow similar to Excel.Supports Excel-compatible .xlsx, .xlsm, and .csv files for seamless sharing.Free, lightweight WPS Office suite reduces manual copy-paste work for large CSV batches.
microsoft office alternative - wps office

Frequently Asked Questions

How do I remove the .csv extension from the source file name in the VBA header?

Use Replace(fileName, ".csv", "") in the macro and write the returned value to row 1 before importing the corresponding second column.

Why are my CSV files imported in a different order than the creation date?

The Dir function typically returns files in file-system name order, not creation-date order. Rename the files in the desired sequence or sort the columns after import.

Can Power Query combine CSV files from a folder and keep the source file name?

Yes, in Excel use Data > Get Data > From File > From Folder and keep the Source.Name column. WPS Spreadsheets users can use the VBA method if Power Query is not available.

Does the macro still work if the first column values are not in the same order across files?

The basic macro copies the first column from the first file and assumes the other files match row by row. Sort or align files first, or use a lookup function if the order differs.

Can WPS Spreadsheets run VBA macros?

Yes, WPS Spreadsheets on Windows includes VBA macro support. Enable the Developer tab, open the VBA editor with Alt+F11, and insert the code in a standard module.