VBA & Macro Problems

Import Data from Multiple Excel Files with a VBA Macro in WPS Spreadsheets

Anum Mushtaq Nov 13, 2026 869 views

Question details

The user needs a VBA macro that imports the values in cells D1 and D2 from about 325 Excel workbooks into one worksheet, placing each file's values into columns A and B.

Import Data from Multiple Excel Files with a VBA Macro

Product: WPS Spreadsheets (WPS Office)
Device & OS: not provided
Scenario: Batch consolidation of values from many source workbooks without opening each file manually.
Observed behavior: Each source workbook contains the required values in D1 and D2; the goal is to collect them row by row in a single destination worksheet.

Before you start

Place all source workbooks in one folder and confirm the destination workbook, destination sheet name, source sheet name, and file extension before running the macro. Test the macro on copies of a few files first to avoid changing original data.

Solution 1Recommended

Use a folder-loop VBA macro to import D1 and D2 from multiple workbooks

Use this solution when you need to consolidate hundreds of Excel files automatically without opening each source workbook manually.

The macro loops through every matching file in a selected folder, opens each source workbook in the background, reads cells D1 and D2, writes those values to the next empty row in columns A and B, and closes the source file without saving changes.

Because it handles about 325 workbooks in one run, keep the source files in a dedicated folder and make sure the worksheet names match the code.

Open the VBA editor in WPS Spreadsheets

Open the destination workbook in WPS Spreadsheets. On Windows press Alt+F11; on Mac press Option+F11 to open the Microsoft Visual Basic for Applications editor.

Insert a standard module

In the VBA editor, click Insert > Module to create a new code module where the import macro will be stored.

Paste and customize the import macro

Create a macro that uses Dir() to loop through files in the folder, Workbooks.Open to open each file, reads the source sheet D1 and D2, writes them to the next destination row in columns A and B, and Workbooks.Close SaveChanges:=False to close each source without saving. Update the folder path, destination sheet name, source sheet name, and file extension such as .xlsx or .xls.

Run the macro

Close the VBA editor and return to WPS Spreadsheets. On Windows press Alt+F8, or on Mac press Option+F8, select the import macro, and click Run.

Use a folder-loop VBA macro to import D1 and D2 from multiple workbooks

Works for large batches: Use a dedicated folder and test on file copies first; the macro writes to the next empty row each time, so repeated runs append data instead of overwriting existing entries.

Solution 2

Test the macro on a small file copy before importing all 325 workbooks

Use this as a safety step when you want to verify the correct cells and worksheet names before processing the full folder.

WPS Spreadsheets VBA macro

Automate multi-workbook data imports with a WPS Spreadsheets VBA macro

WPS Spreadsheets supports VBA macros for looping through a folder, opening each Excel-compatible workbook, reading cells D1 and D2, and writing the values into one destination worksheet. Update the folder path and sheet names, then run the macro to consolidate all 325 files without manual copying.

1. Open WPS Spreadsheets and enable macro access: Open the destination workbook in WPS Spreadsheets. Ensure macro execution is allowed for the workbook when prompted.
2. Open the VBA editor: Press Alt+F11 on Windows or Option+F11 on Mac to open the VBA editor.
3. Insert a module and paste the import code: Click Insert > Module, then paste a macro that loops through files, opens each workbook, reads D1 and D2, writes them to columns A and B, and closes the source without saving.
4. Update the folder path and worksheet names: In the macro code, set the folder path to the folder containing your 325 files and adjust the source sheet name and destination sheet name.
5. Run the macro: Return to WPS Spreadsheets, press Alt+F8 on Windows or Option+F8 on Mac, select the import macro, and click Run.

Imports D1 and D2 from 300+ workbooks into one worksheet automaticallySupports .xlsx, .xls, and .xlsm files for Microsoft Excel compatibilityCloses source workbooks without saving so original data remains unchangedFree macro-enabled spreadsheet editing in WPS Office on Windows and macOSUses a familiar VBA workflow, reducing migration effort from Microsoft Excel

microsoft office alternative - wps office

Frequently Asked Questions

Can I run this VBA import macro in WPS Office on Mac?

Yes. Open the VBA editor with Option+F11 and run the macro with Option+F8. Update the folder path and file extension just as you would on Windows, and test on file copies first.

How do I change the macro if my source values are not in D1 and D2?

In the macro code, replace the D1 and D2 cell references with the actual source cells you need. Also verify the source sheet name so the macro reads from the correct worksheet.

Will the macro save changes to the source workbooks?

No, if the code uses Workbooks.Close SaveChanges:=False each source workbook closes without saving. Keep this line to avoid modifying the original files.

What should I do if the macro imports data from the wrong sheet?

Check the source sheet name in the code and make sure every source workbook uses that exact sheet name. Run the macro on a small test folder before processing all 325 files.

Does WPS Spreadsheets support this VBA folder loop for .xlsx and .xls files?

Yes, WPS Spreadsheets can process Excel-compatible formats including .xlsx, .xls, and .xlsm. Set the file extension in the Dir() pattern to match the files you want to import.

Excel & Data Problems