Consulting

Results 1 to 10 of 10

Thread: [VBA]Delete page with given text

  1. #1

    [VBA]Delete table/page with given text

    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/...-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
    Last edited by johngalvin; 09-20-2019 at 08:55 AM.

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  3. #3
    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
    Last edited by johngalvin; 09-20-2019 at 08:53 AM.

  4. #4
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  5. #5
    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

  6. #6
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  7. #7
    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
    Last edited by johngalvin; 09-20-2019 at 11:20 AM.

  8. #8
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  9. #9
    Hi Dave.
    Many thanks! No problem.
    Last edited by johngalvin; 09-20-2019 at 12:38 PM.

  10. #10
    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 !

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •