[VBA]Delete page with given text

[johngalvin]

Hello everyone!

Will someone be kind and help me with a VBA code, I am new into this and want to learn.
Is there a way to delete a Microsoft Word Document table/page by giving a specific keyword by creating a VBA in Excel? I am asking this because I am working in a Music Store and we have to get rid of some table/page in Multiple Word Documents, and I guess it would be easier by a VBA in Excel (by searching for a given text and delete), rather than copy pasting the VBA in each Word.

I found this on SOF https://stackoverflow.com/questions/33058736/word-macro-to-delete-pages-with-specific-text
but seems to work only in Word VBA.

Can someone help? Thank you in advance

Below is what I've tried so far, but unfortunately it doesen't do anything after searching the word "Solo", so that's wrong and it is not what I want. Perhaps what my code is missing is to jump (goTo) to the table once the text was found then to delete as below



With .Find
.Forward = False
.ClearFormatting
.MatchWholeWord = True
.MatchCase = True
.Wrap = wdFindContinue
.Text = "Solo"
.Execute
If .Execute = True Then
Table.delete
End If
End With



Best,
John

[Dave]

Your request is confusing. U want to delete from multiple documents... same directory? U want to "delete" tables and/or pages.... what happens in the void left over? U can loop pages/tables in Word from Xl and use the Instr function to determine if some word exists within the table/page but then what? Good luck. Dave

[johngalvin]

Hey Dave
Sorry about that

It is okay if I can make the Excel VBA search for a text ("Solo"). The 1st appearance of it will be in a table in all the Word Docs (because that's what it is) , so if I can figure out how to make an Excel VBA to search for "Solo" in a Word Document and when found to delete that table it would be perfect, because all I will have to do is to rename the Word Documents afterwards.

In short want to do something like in the Stackoverflow link but instead of deleting the page, it should delete the whole table where that text is found, and it shouldn't go forward than the 1st result.

Many thanks,
John

[Dave]

John are all the Word docs in 1 folder/path? Is there only 1 occurrence of the text in each doc always within a table? Is the text always "Solo"? Dave

[johngalvin]

Hey Dave,
Yes to all your questions.
It's okay if I can do for one of them, I'll rename then and add in the code variables for each.
Just let me know if you can help to do it for one Word Doc (Sort of like in Stackoverflow example but for table instead of page)
Thank you!

Best,
John

[Dave]

John this seems like it should work. Change the folder directory to suit. Dave

Option Explicit
Sub XLWordTable()
Dim WrdApp As Object, Cnt As Integer, FileStr As String
Dim WrdDoc As Object, TblCell As Variant, SearchWord As String
Dim FSO As Object, FolDir As Object, FileNm As Object
'*** SearchWord is case sensitive
SearchWord = "Solo"
On Error GoTo ErFix
Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = False
Set FSO = CreateObject("scripting.filesystemobject")
'***change directory to suit
Set FolDir = FSO.GetFolder("D:\testfolder")
'loop files
For Each FileNm In FolDir.Files
If FileNm.Name Like "*" & ".docx" Then
FileStr = CStr(FileNm)
Set WrdDoc = WrdApp.Documents.Open(FileStr)
'loop tables
For Cnt = 1 To WrdApp.ActiveDocument.Tables.Count
'loop through table cells
For Each TblCell In WrdApp.ActiveDocument.Tables(Cnt).Range.Cells
If InStr(TblCell.Range, SearchWord) Then
WrdApp.ActiveDocument.Tables(Cnt).Delete
GoTo Below
End If
Next TblCell
Next Cnt
Below:
'close and save doc
WrdApp.ActiveDocument.Close savechanges:=True
Set WrdDoc = Nothing
End If
Next FileNm
Set FolDir = Nothing
Set FSO = Nothing
WrdApp.Quit
Set WrdApp = Nothing
MsgBox "Finished"
Exit Sub
ErFix:
On Error GoTo 0
MsgBox "error"
Set FolDir = Nothing
Set FSO = Nothing
Set WrdDoc = Nothing
WrdApp.Quit
Set WrdApp = Nothing
End Sub

[johngalvin]

Hi Dave,

Many thanks ! Much appreciated !

Works like charm ! Made my day ! Thanks !!!

Will this also work for things like Table of Content? Or for that I should think of a delete Page method? Like can the above code also be adjusted to delete the page?


Sort of like this:

'
loop pages
For Cnt = 1 To WrdApp.ActiveDocument.Pages.Count
'loop through Pages
For Each Page In WrdApp.ActiveDocument.Pages(Cnt).Range
If InStr(Pages.Range, SearchWord) Then
WrdApp.ActiveDocument.Page(Cnt).Delete
GoTo Below
End If
Next Page
Below:

Best,
John

[Dave]

You are Welcome John. As far as deletion of pages, Word is a funny thing. U probably have to loop paragraphs to determine where the page starts and ends, set that to a range and then delete the range/page. So possibly doable but not that easy unless Macropod or others have an easier fix. Good luck. Dave

[johngalvin]

Hi Dave.
Many thanks! No problem.

[johngalvin]

Hello Dave.

It's okay, just to let you know, the problem has been Solved. The topic can be marked as Solved.
Many thanks ! Really appreciated !