How to Copy Word Tables into Separate Excel Worksheets with a VBA Macro in WPS Office
Question details
The user needs a VBA macro that opens a selected Word document, loops through each table, and places every table on its own Excel worksheet automatically.
- Product
- WPS Office
- Device & OS
- not provided
- Scenario
- The user is working with Word documents containing multiple tables and wants to transfer each table into a separate Excel worksheet without copying them manually.
- Observed behavior
- Manual copying is repetitive and easy to break; the requested macro should automate file selection, table looping, worksheet creation, and object cleanup.
Before running the macro, save the WPS Spreadsheets workbook as a macro-enabled workbook (.xlsm), enable macros if prompted, and keep the target Word document in a known folder so you can select it in the file dialog.
Create a VBA Macro in WPS Spreadsheets to Import Each Word Table
Use this method when you need a repeatable, automated transfer from a Word document to separate worksheets in WPS Spreadsheets.
This macro uses the VBA editor in WPS Spreadsheets and automates the Word document through COM automation. It opens a file picker, connects to Word, loops through Document.Tables, and copies each table into a newly created worksheet.
In WPS Spreadsheets, click the Developer tab and select Visual Basic, or press Alt + F11 to open the VBA editor.
In the Project Explorer, right-click VBAProject, choose Insert > Module, and open the new module window.
Paste the following VBA code into the module: Sub CopyWordTablesToSheets() Dim wdApp As Object Dim wdDoc As Object Dim wdTable As Object Dim ws As Worksheet Dim filePath As Variant Dim i As Integer filePath = Application.GetOpenFilename("Word Documents (*.docx; *.doc), *.docx; *.doc") If filePath = False Then Exit Sub Set wdApp = CreateObject("Word.Application") Set wdDoc = wdApp.Documents.Open(filePath) For i = 1 To wdDoc.Tables.Count Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)) ws.Name = "Table_" & i wdDoc.Tables(i).Range.Copy ws.Range("A1").Select ws.PasteSpecial Format:="Text" Next i Application.DisplayAlerts = False If ThisWorkbook.Worksheets.Count > 1 Then ThisWorkbook.Worksheets(1).Delete End If Application.DisplayAlerts = True wdDoc.Close False wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub
Place the cursor inside the Sub CopyWordTablesToSheets procedure and press F5, or click Run > Run Sub/UserForm. When prompted, select the Word document that contains the tables.
Switch back to WPS Spreadsheets and confirm that each Word table is placed on a worksheet named Table_1, Table_2, and so on. Rename or delete sheets as needed.
Manually Copy Each Word Table into a Separate Worksheet
Use this manual route when you only have a few tables and do not want to run a macro.
Automate Word Table Import in WPS Office
Run the VBA import macro in WPS Spreadsheets to open a Word document and create a separate worksheet for every table, while keeping the workflow compatible with .docx and .xlsx files.
- 1. Open WPS Spreadsheets: Launch WPS Office and create a new workbook in WPS Spreadsheets.
- 2. Open the VBA editor: Click Developer > Visual Basic or press Alt + F11 to open the macro editor.
- 3. Insert and run the macro: Insert a new module, paste the Word-table import macro, and press F5 to run it.
- 4. Select the Word document: Choose the .docx or .doc file that contains the tables you want to import.
- 5. Check the worksheets: Confirm each Word table appears on its own worksheet and save the workbook as a macro-enabled file.
Frequently Asked Questions
Can I run this VBA macro without Microsoft Word installed?
The standard code uses CreateObject("Word.Application"), so a Word-compatible application or Microsoft Word must be installed and registered on Windows. Test with a small document first if only WPS Writer is available.
Why do I get a 'User-defined type not defined' error?
Use late binding: declare wdApp and wdDoc as Object and use CreateObject("Word.Application") instead of declaring them as Word.Application. Do not set a reference to the Word object library.
How do I stop Word from running in the background after the macro?
Add wdDoc.Close False, wdApp.Quit, and set the objects to Nothing at the end of the macro. After a failed run, check Task Manager for WINWORD.EXE and end it manually if needed.
Can I preserve the original Word table formatting?
Yes. Use wdDoc.Tables(i).Range.Copy and experiment with PasteSpecial. For plain data, use text paste; for formatted tables, use a normal paste or paste values depending on your desired result.
Does WPS Office support macro-enabled workbooks?
Yes, WPS Spreadsheets supports .xlsm files and VBA macros on Windows. If a macro security prompt appears, enable content or macros to allow the import macro to run.
