How to Export Matching Text from Word VBA to a New Excel Workbook in WPS Office
Question details
The user needs to run a Word VBA macro that uses late binding to find text patterns such as The_1234 and write each match to a separate row in a new Excel workbook.
- Product
- WPS Office Writer and Spreadsheets
- Device & OS
- not provided
- Scenario
- Automating data extraction from a Word document into an Excel workbook so matched text patterns are organized into separate rows.
- Observed behavior
- The macro should create a new Excel workbook, add a worksheet, and paste each found match into the next row.
Open the document in WPS Writer and enable the Developer tab from the ribbon so that the Visual Basic Editor and macro commands are available.
Run a Late-Bound Word VBA Macro to Write Each Match to a New Excel Workbook
Use this solution when you want the export to run from WPS Writer without adding an Excel object library reference, so the same VBA code can create and populate a new workbook.
Late binding uses CreateObject("Excel.Application") instead of a compile-time Excel reference, which avoids version-specific reference issues and works in WPS Office VBA.
The value -4167 is the Excel constant xlWBATWorksheet; passing it to Workbooks.Add creates a workbook with one worksheet instead of the default number configured in Excel Options.
In WPS Writer, click the Developer tab and then click Visual Basic, or press Alt+F11 to open the VBA editor.
In the Project Explorer, select the current document, click Insert > Module, and paste the export macro into the new module.
Use late-bound code such as Set xlApp = CreateObject("Excel.Application"), Set xlWb = xlApp.Workbooks.Add(-4167), and Set xlWsh = xlWb.Worksheets(1).
Set Find.Text to the target pattern such as The_1234, execute the search, and increase the row counter r by 1 for each match.
Copy the found range and use xlWsh.Range("A" & r).PasteSpecial Format:="Text", then show the generated workbook with xlApp.Visible = True.
Fix the .Execute Invalid or Unqualified Reference Error in the Search Loop
Use this troubleshooting solution when the macro creates an Excel workbook but skips the copy-and-paste loop and reports that .Execute is invalid or unqualified.
Use WPS Office VBA to Export Word Matches to a New Spreadsheet Workbook
Run the same late-bound export macro in WPS Office: WPS Writer provides the VBA editor and WPS Spreadsheets opens the generated workbook, so you can automate pattern extraction and save the result as a standard Excel .xlsx file.
- 1. Install WPS Office: Download WPS Office from wps.com and install the suite that includes Writer and Spreadsheets.
- 2. Open the Word document: Launch WPS Writer, open the document containing the text patterns, and enable the Developer tab if it is not visible.
- 3. Add and run the export macro: Press Alt+F11 to open the VBA editor, insert the late-bound macro that creates the Excel instance with Workbooks.Add(-4167), and run it.
- 4. Save the generated workbook: Review the matches in the new WPS Spreadsheets workbook, then save it as .xlsx from File > Save As.
Frequently Asked Questions
Why use late binding instead of adding an Excel object library reference?
Late binding uses CreateObject("Excel.Application") and avoids compile-time references, so the same macro is less likely to break when the Excel version changes or when it is run in WPS Office VBA.
What does Workbooks.Add(-4167) do in WPS VBA?
The value -4167 is the Excel constant xlWBATWorksheet, so Workbooks.Add(-4167) creates a new workbook that contains one worksheet instead of the default worksheet count set in Excel Options.
Why does .Execute show an invalid or unqualified reference?
This usually happens when the Find object is not qualified with a valid Range. Use Set rng = ActiveDocument.Content and then rng.Find.Execute, or move the loop outside the With block so the Find call refers to the correct object.
Can this Word VBA macro run in WPS Office even though it was written for Microsoft Word?
Yes, WPS Office supports a VBA editor and Excel-compatible automation in Writer and Spreadsheets. The late-bound CreateObject approach works for opening a workbook and writing rows in WPS Spreadsheets.
How do I avoid pasting unwanted formatting into the Excel workbook?
Use the PasteSpecial method with Format:="Text" when writing each match, as in xlWsh.Range("A" & r).PasteSpecial Format:="Text", to paste the value as text.
