Automatically Delete Tables in Microsoft Word using Excel VBA
There are some cases that we want to delete tables in multiple Microsoft Word files but doing this manually takes time and even troublesome especially if we have a large number of files. Good thing is that we can automatically remove all the tables in Microsoft Word through VBA macro program that we could simply run and get the result immediately in just few seconds.
Let’s take a scenario where we have a business that we need to prepare and send several invoice to the customers on a regular basis and we want to create a separate report for the summary. Assuming that we have an invoice format similar to the image shown below and we just want to get the information (highlighted in red) and remove all the succeeding tables and other related details. If we do this manually, we need to go open the Microsoft Word document, select each table and hit delete key. Can you imagine how much time do you need to waste if you have more than 10 tables on each document?
The sample layout below contains the summary information and the tables with the item breakdown information. We will be using this as a sample document.
Finally, our goal is to have the following information to be used as a summary report. We would like to have this summary layout in all of the invoices that we have generated in the past transactions. However, creating a new document and re-typing this information will take too much time and that we have decided to use all the generated invoices in Microsoft Word by just removing the tables along with the item detail information. Sample end result shown in below image.
Definitely, we can achieve this by creating a simple VBA macro program code. The procedure below will walk you through the coding process and the preparation that needs to be done prior to executing the macro program.
1. Gather all the Microsoft Word documents with tables that we want to remove and store into a specific directory location in your local drive. Make sure that there is no document left opened on this folder when executing the code to avoid getting unexpected runtime error.
2. Writing the VBA macro code
Sub DeleteTable() Dim FSystem, strFolder, strFiles, MSWordApp Dim pathLocation As String Dim wordTbl As Table Set FSystem = CreateObject("Scripting.FileSystemObject") pathLocation = "C:\Invoice" Set strFiles = FSystem.GetFolder(pathLocation).Files Set MSWordApp = CreateObject("word.Application") For Each File In strFiles MSWordApp.Documents.Open (File.Path) For Each wordTbl In MSWordApp.ActiveDocument.tables wordTbl.Delete Next MSWordApp.ActiveDocument.Save MSWordApp.ActiveDocument.Close Next End Sub
2.1 Create an instance of the FileSystemObject.Set FSystem = CreateObject("Scripting.FileSystemObject")
Related to “Find and List All Files and Folders in a Directory”.
2.2 Create/ define a variable and store the path location where the Microsoft Word documents are located. This will be used as a source folder when retrieving all the target files.pathLocation = "C:\Invoice" Set strFiles = FSystem.GetFolder(pathLocation).Files
2.3 Make a loop to read all the files in the specified folder location. The program will get and open each file from the source folder. Refer to line # 1 and 13 for the “For Loop” statement. Basically, it will go through the files one at a time and open it in the background (See Line# 2) as if you are opening a file on Microsoft Word Application. Make sure that you close the document instance for every round of loop to avoid leaving the file actively opened in the background (See Line # 10 and 11).For Each File In strFiles MSWordApp.Documents.Open (File.Path) For Each wordTbl In MSWordApp.ActiveDocument.tables wordTbl.Delete Next MSWordApp.ActiveDocument.Save MSWordApp.ActiveDocument.Close Next
2.4 Make another loop to check the existence of all the tables inside the file and remove the tables one by one. Using the “Delete” method, it simply remove the tables associated to the active document instance.For Each wordTbl In MSWordApp.ActiveDocument.tables wordTbl.Delete Next
One thought on “Automatically Delete Tables in Microsoft Word using Excel VBA”