VBA & Macro Problems

How to Use Partial File Names in WPS Office VBA to Copy Matching Files

Chanuka Geekiyanage Nov 13, 2026 869 views

Question details

The user needs a VBA procedure in WPS Office that copies files whose names begin with values in column D, while excluding the destination folder from the search.

How to Use Partial File Names in WPS Office VBA to Copy Matching Files

Product: WPS Spreadsheets VBA
Device & OS: not provided
Scenario: The user has a source folder with files and a worksheet column D containing partial file identifiers, and wants to automate copying matching files to a Found Files subfolder.
Observed behavior: The macro should enumerate the source folder, match file names by leading partial text, create the destination folder if needed, and avoid searching the destination folder.

Before you start

Open the workbook that contains the partial file names in column D and confirm the source folder path. If the destination folder is inside the source folder, note its name so the macro can avoid copying from it.

Solution 1Recommended

Use a Dir Loop and Like Condition to Copy Files by Partial Name

Use this method when you want a native VBA macro that scans the source folder, matches file names against column D values, and copies matches to a Found Files subfolder while skipping that destination folder.

The macro reads each cell in column D and builds a partial-match test with Like CStr(cell.Value) & "*". A file is copied only when its name begins with the current column D value, so extra text in the file name does not prevent a match.

Open the VBA editor

Open the WPS Spreadsheets workbook that contains the file names in column D. Press Alt+F11, or click the Developer tab and choose Visual Basic, to open the VBA editor.

Insert a module

In the VBA editor, click Insert > Module to create a new code module.

Paste and adjust the VBA code

Paste the following procedure into the module. Change srcFolder to the source folder path, and change Sheet1 and the column D range if your workbook uses different names. Sub CopyPartialMatches() Dim srcFolder As String Dim destFolder As String Dim fileName As String Dim cell As Range Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") srcFolder = "C:\Source\" destFolder = srcFolder & "Found Files\" If Dir(destFolder, vbDirectory) = "" Then MkDir destFolder End If fileName = Dir(srcFolder & "*.*") Do While fileName <> "" For Each cell In ws.Range("D2:D" & ws.Cells(ws.Rows.Count, "D").End(xlUp).Row) If fileName Like CStr(cell.Value) & "*" Then FileCopy srcFolder & fileName, destFolder & fileName Exit For End If Next cell fileName = Dir Loop End Sub

Run the macro

Close the VBA editor and return to WPS Spreadsheets. Press Alt+F8, select CopyPartialMatches, and click Run.

Use a Dir Loop and Like Condition to Copy Files by Partial Name

Matching files copied: The macro copies each file whose name starts with a value from column D into the Found Files subfolder. The destination subfolder is not searched because the Dir loop only enumerates files in srcFolder, and the macro creates the destination folder if it does not exist.

WPS Office VBA macro

Copy Files by Partial Name with WPS Office VBA

Use WPS Spreadsheets and its built-in VBA editor to run a macro that reads column D values, finds matching files in a source folder, and copies them to a Found Files subfolder automatically.

1. Download and install WPS Office: Get WPS Office from the official WPS site and install it on your computer.
2. Open your workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook that has partial file names in column D.
3. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic to open the built-in VBA editor.
4. Paste and run the macro: Insert a module, paste the partial-match VBA code, adjust the source folder path, and press Alt+F8 to run the macro.

Built-in VBA editor in WPS Spreadsheets lets you automate partial file name matching without extra add-ins.Opens, runs, and saves Microsoft Excel macro-enabled workbooks, including .xlsm files, for seamless VBA reuse.Free and lightweight Office suite with a familiar interface, making macro tasks easy to migrate from Excel.MkDir and FileCopy support in WPS VBA lets you create the Found Files folder and copy matches automatically.

Frequently Asked Questions

Can the VBA macro match a partial file name in the middle instead of only at the beginning?

Yes. Change the Like pattern from CStr(cell.Value) & "*" to "*" & CStr(cell.Value) & "*" to match anywhere in the file name.

How do I stop the destination folder from being searched?

Use Dir with no folder attribute or loop through folder.Files with FileSystemObject, because both enumerate files directly in the source folder rather than files inside a subfolder such as Found Files.

Why does FileCopy fail with a file not found or permission error?

Check that the source folder path ends with a backslash, the destination folder exists or is created with MkDir, and the destination file is not open, read-only, or locked by another program.

Can I copy files to a folder outside the source folder?

Yes. Set destFolder to any writable folder path, such as "C:\Found Files\", and make sure it exists or let the macro create it with MkDir.

Excel & Data Problems