WPS Spreadsheet: Copy Data from Multiple Excel Files and Save Each Result
Question details
The user needs to automate copying selected columns from multiple Excel or CSV files into a template and save one output file for each source file.
- Product
- WPS Spreadsheet with VBA macros
- Device & OS
- not provided
- Scenario
- A folder contains multiple source files with similarly structured worksheets. The user wants to process them in a batch, map source columns to template columns, save each result using the source filename, and generate hyperlinks from column values.
- Observed behavior
- The task requires a VBA loop using Dir, Workbooks.Open, range copying, template saving, and a HYPERLINK formula; no specific error message is provided.
Confirm that VBA macros are enabled in WPS Spreadsheet and keep the template workbook open with the expected worksheet name. Replace the sample folder path, workbook name, worksheet names, column mappings, and output folder before running the macro.
Use VBA to process every source file and save a separate template result
Use this method when many Excel or CSV files share the same structure and must be processed automatically with the same column mapping.
The macro uses Dir to enumerate files, opens each source workbook, copies selected columns into the template, and saves a separate result with the source filename. Clear the template’s previous data inside each loop if every output must contain only the current file’s records.
The example assumes the source worksheet is named AA_Result, the template worksheet is named listings, and the template workbook is already open as Template1.xlsx. Adjust these values for your files.
In WPS Spreadsheet, open the template workbook, press Alt+F11, choose Insert > Module, and paste the macro into the new module.
Use this VBA code, then update folderPath, outputPath, workbook names, worksheet names, and source and destination columns: Sub ProcessFilesInFolder() Dim folderPath As String, outputPath As String, fileName As String Dim sourceBook As Workbook, templateBook As Workbook Dim sourceSheet As Worksheet, destinationSheet As Worksheet Dim lastRow As Long, destinationRow As Long, resultName As String folderPath = "C:\\Users\\Jean\\Path\\To\\Folder2\\" outputPath = folderPath & "Results\\" Set templateBook = Workbooks("Template1.xlsx") Set destinationSheet = templateBook.Worksheets("listings") fileName = Dir(folderPath & "*.csv") Do While fileName <> "" Set sourceBook = Workbooks.Open(folderPath & fileName) Set sourceSheet = sourceBook.Worksheets("AA_Result") destinationSheet.Range("C2:H1048576").ClearContents lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row destinationRow = 2 sourceSheet.Range("F2:F" & lastRow).Copy destinationSheet.Range("F" & destinationRow) sourceSheet.Range("E2:E" & lastRow).Copy destinationSheet.Range("C" & destinationRow) sourceSheet.Range("H2:H" & lastRow).Copy destinationSheet.Range("H" & destinationRow) destinationSheet.Range("F2:F" & lastRow).NumberFormat = "d/m/yyyy h:mm" destinationSheet.Range("H2:H" & lastRow).NumberFormat = "d/m/yyyy h:mm" resultName = outputPath & Replace(fileName, ".csv", ".xlsx") templateBook.SaveCopyAs resultName sourceBook.Close SaveChanges:=False fileName = Dir Loop End Sub
Close the VBA editor, return to WPS Spreadsheet, press Alt+F8, select ProcessFilesInFolder, and click Run. Make sure the Results folder exists before starting the process.
In the destination sheet, select the first link cell, such as C2, enter =HYPERLINK("https://websphere.net/t5/UID?="&B2,"Link"), and fill the formula down to create a URL for each value in column B.
Import and map the files manually in WPS Spreadsheet
Use this approach for a small number of files or when VBA is disabled and each source file can be reviewed before saving.
Process Multiple Excel Files with WPS Spreadsheet
WPS Spreadsheet can open Excel and CSV files, run compatible VBA workflows when macro support is enabled, map data into a template, save separate results, and create hyperlinks with standard spreadsheet formulas.
- 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheet.
- 2. Prepare the template: Open the template workbook and confirm its worksheet names and destination columns.
- 3. Enable and run VBA: Open the VBA editor with Alt+F11, insert the batch-processing macro, customize the paths, and run it with Alt+F8.
- 4. Create the links: Enter the HYPERLINK formula in the destination sheet and fill it down for all processed rows.
Frequently Asked Questions
How do I make the macro process Excel workbooks instead of CSV files?
Change Dir(folderPath & "*.csv") to Dir(folderPath & "*.xlsx") or Dir(folderPath & "*.xlsm"), then update the Replace expression so the output extension matches the source type you want to convert.
Why does the macro need the exact worksheet names?
The code calls sourceBook.Worksheets("AA_Result") and templateBook.Worksheets("listings"). If either name differs, update the text inside the quotation marks to match the worksheet tabs exactly.
How can I stop data from previous files appearing in each result?
Clear the template destination range inside the Do While loop before copying the current source data, for example with destinationSheet.Range("C2:H1048576").ClearContents. This gives every saved copy only the current file’s records.
Where should I place the HYPERLINK formula?
Enter =HYPERLINK("https://websphere.net/t5/UID?="&B2,"Link") in the first destination link cell, such as C2, and drag the fill handle down. Replace B2 with the cell containing the value used in the URL.
What should I do if Alt+F11 or Alt+F8 does not open the macro tools?
Check whether VBA or macro execution is available and enabled in your WPS installation. If it is unavailable, use WPS Spreadsheet’s File > Open, Copy, Paste, and Save As commands to process a smaller number of files manually.
