Use VBA Dir in WPS Office to Find Excel Workbook Files
Question details
The user wants to understand how the VBA Dir function in WPS Office searches a folder for Excel workbook files and returns matching filenames.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is writing or debugging a VBA macro that needs to locate Excel workbook files in a folder.
- Observed behavior
- The Dir function searches the specified folder using the pattern *.xls* and returns the first matching filename; subsequent Dir calls return the next matches until no more files are found.
Make sure the folder path in your VBA code ends with a backslash and that the target folder contains files with extensions such as .xls, .xlsx, .xlsm, and .xlsb. Also confirm that macros are enabled in WPS Office so the VBA code can run.
Use the VBA Dir Function to Loop Through Excel Workbook Files
Use this solution when your WPS Office macro must discover and process all Excel workbook files in a folder.
The Dir function returns only the filename, not the full path. You must combine the folder path and the returned filename when opening each workbook.
In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor and insert a new module.
Declare a string variable such as MyPath for the target folder, then use Dir(MyPath & "\*.xls*", vbNormal) to match .xls, .xlsx, .xlsm, and .xlsb files.
Assign the result to strFilename and call Dir again without arguments inside a loop to get the next matching filename until an empty string is returned.
Use Workbooks.Open(MyPath & "\" & strFilename) to open each found workbook using the complete folder path and filename.
Find Excel Files with VBA Dir in WPS Office
Use WPS Spreadsheets and its VBA editor to run the Dir function against a folder pattern such as *.xls*, retrieve matching workbook filenames, and open or process them in a loop.
- 1. Download and install WPS Office: Download WPS Office from the official website and install it on your Windows PC.
- 2. Open WPS Spreadsheets and enable macros: Launch WPS Spreadsheets, open a workbook, and enable macros when prompted so VBA code can run.
- 3. Open the VBA editor: Press Alt+F11 to open the VBA editor and insert a module for your Dir loop.
- 4. Run the Dir file search macro: Paste the Dir code with the correct folder path and run the macro to list or open matching Excel workbooks.
Frequently Asked Questions
Does Dir with "*.xls*" include .xlsx, .xlsm, and .xlsb files?
Yes. The pattern *.xls* matches any file extension beginning with xls, including .xls, .xlsx, .xlsm, and .xlsb.
Why does Dir return only the first matching filename?
Dir returns the first match on the initial call. To get the next matches, call Dir again without arguments inside a loop until it returns an empty string.
Do I need the full path when opening a file found by Dir?
Yes. Dir returns only the filename, so combine the folder path and filename, for example Workbooks.Open(MyPath & "\" & strFilename).
What happens when Dir finds no matching Excel files?
When no files match the pattern, Dir returns an empty string immediately, so your loop should test for that before trying to open a workbook.
Can I use VBA Dir in WPS Office on Windows?
Yes, WPS Office supports VBA macros on Windows. Make sure macros are enabled and use the VBA editor opened with Alt+F11.
