Copy Linked Word Files to One Folder with VBA in WPS Spreadsheets
Question details
The user needs to automate copying hundreds of Word files that are referenced by hyperlinks in an Excel worksheet into a single destination folder.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains hyperlinks to many Word documents, and the user wants to batch-copy those linked files instead of opening or copying each one manually.
- Observed behavior
- The hyperlinks are present in the worksheet, but manually copying the referenced Word files is time-consuming and error-prone.
Test the macro on a backup copy of your workbook first, and confirm that the hyperlink addresses are accessible from your computer, because path formats such as file:/// URLs, relative paths, or network paths may need adjustments.
Automate Copying Linked Word Files with a VBA Macro
Use this recommended approach when you have many hyperlinked Word documents in a worksheet and need to copy every linked file into one folder.
This VBA macro scans the used range of the active WPS Spreadsheets worksheet, reads each cell hyperlink address, strips common file:/// prefixes, derives the Word file name, and copies the source file to the selected destination folder with FileCopy.
It works best when the hyperlinks point to local or network file paths. If your links use a different URL encoding, adjust the path-cleaning lines before running the macro.
In WPS Spreadsheets, open the workbook that contains the hyperlinks, then press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
In the VBA editor, click Insert > Module, then paste the following macro: Option Explicit Sub CopyLinkedWordFiles() Dim destFolder As String Dim cell As Range Dim filePath As String Dim fileName As String With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Select destination folder" If .Show <> -1 Then Exit Sub destFolder = .SelectedItems(1) End With For Each cell In ActiveSheet.UsedRange If cell.Hyperlinks.Count > 0 Then filePath = cell.Hyperlinks(1).Address filePath = Replace(filePath, "file:///", "") filePath = Replace(filePath, "/", "\") fileName = GetFileName(filePath) If Len(Dir(filePath)) > 0 Then FileCopy filePath, destFolder & "\" & fileName End If End If Next cell MsgBox "Copy complete." End Sub Function GetFileName(fullPath As String) As String GetFileName = Mid(fullPath, InStrRev(fullPath, "\") + 1) End Function
Click inside CopyLinkedWordFiles in the VBA editor and press F5, or return to WPS Spreadsheets, click Developer > Macros, select CopyLinkedWordFiles, and click Run.
Open the destination folder you selected and confirm that the linked Word documents were copied. If any files were skipped, inspect their hyperlink addresses and adjust the path extraction code.
Batch-Copy Hyperlinked Word Files with WPS Spreadsheets VBA
Use a reusable VBA macro in WPS Spreadsheets to select a destination folder, scan worksheet hyperlinks, extract source paths, and copy hundreds of linked Word documents into one location without manual file-by-file work.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the hyperlinks to your Word files.
- 2. Open the VBA editor: Press Alt+F11 or click Developer > Visual Basic to open the WPS VBA editor.
- 3. Insert the batch-copy macro: Click Insert > Module, paste the FileDialog and FileCopy VBA code, then save the workbook as a macro-enabled file.
- 4. Run the macro: Press F5 or click Developer > Macros, select CopyLinkedWordFiles, and choose the destination folder when prompted.
- 5. Check the destination folder: Open the selected folder and verify that the linked Word documents were copied successfully.
Frequently Asked Questions
Can WPS Spreadsheets run VBA macros that copy files from hyperlinks?
Yes. WPS Spreadsheets includes a VBA editor. Open it with Alt+F11, insert a module, paste the macro, and run it to read hyperlinks and copy the referenced files.
Why are some hyperlinked Word files skipped by the macro?
The most common reason is the hyperlink address format. The sample macro removes a file:/// prefix and converts forward slashes to backslashes. If your links use relative paths, URLs, or different separators, adjust the path-cleaning lines before running it.
How does the macro choose where to copy the files?
The macro uses Application.FileDialog with msoFileDialogFolderPicker to show a folder picker. You select the destination folder when the macro runs, and it copies each linked file into that folder.
Does this method work for both .doc and .docx Word files?
Yes. FileCopy copies the file itself regardless of extension, so the method works for .doc, .docx, and other file types as long as the source path is valid and accessible.
