MS Word Macro Code

Posted on 2014-08-11
Last Modified: 2014-08-18
I have an MS Word Document that is created from an MS Access Report import (please see attached).

I would like to create a Macro that:

1.      Automatically opens each time it is updated (receives an import from Access)
2.      Deletes all text within any Paragraph that begins with 'Delete—'  
3.      Shifts all remaining text appropriately to the left and up so that the document has an appropriate structure (so that doesn’t have any major breaks between paragraphs and/or words—as it appears to have right now).

If someone could give me an MS Word Macro code to make these things happen; I would be most grateful.

Note:  In case you need it…

The Access Database is called:  Matts Cain Database
It is located at:   C:\Users\Matt\Desktop\WUCAINS Final\Mega Folder\Tony\Matts CAIN Database
The report is titled:  rptMaster
Question by:mdstalla
    LVL 76

    Accepted Solution

    If you are already creating or updating a Word document from Access, I suggest that you put the code in Access and run it from there. Call this from your existing code after the update:
    Sub DeleteDelete(doc As Word.Document)
        With doc.Range.Find
            .MatchWildcards = True
            .Text = "Delete-*^13"
            .Replacement.Text = ""
            .Execute Replace:=wdReplaceAll
        End With
    End Sub

    Open in new window


    Author Comment

    I'm sorry, I think I might be doing something wrong.

    Let me give you a more clarified and detailed explanation of what I'm trying to do:

    In my Access Database, suppose I have a Form with a Button; and a Report (Report1).  What I would like to have happen is…. When I click the button:

    1.      I get a 'dialog box' that reads:  "Would you like to generate a report based on this case?"  There's a 'Yes' and a 'No' button.  If 'No' is clicked, the Access Database simply stays where it's at.  If 'Yes' is selected, the Access Database is saved; Report1 is exported to a MS Word Document (WordCatch1); and then the Access Database is closed.

    2.      Word Documents that are created from an Import from Access have formatting problems (there are unnecessary spaces between words and large gaps between paragraphs).  What I would like to happen next is for the Word Document to automatically open; and for the formatting to autocorrect itself so that there's no unnecessary spaces between words and there's no unnecessary gaps between paragraphs (please reference the attached World Document to see what I mean by 'formatting problems.'

    3.      You will notice that some paragraphs begin with the word 'Delete--.'  I would like any paragraph that begins with 'Delete—' to be entirely deleted and the document should automatically adjust as described in #2.

    4.      Finally, I would like a Save Window to appear so the user can chose a local file to save this document.

    Note:  WordCash1 is located at:  C:\Users\Matt\Desktop\WordCatch1.docx.rtf

    Question:  Can I place a VBA code within the Access Button to make all this happen?  If so, can you provide that code to me?  If not, can you walk me though how to make this happen (please be detailed on exactly where place codes).

    Thank you so much!
    LVL 76

    Expert Comment

    The problem with putting the code in Word is that there is no way that Word will 'know' what has just been done in Access. However if you tick the checkbox to open the database file afterwards, and if you put this code in the ThisDocument module of the Normal template, it will run when Word opens the document.
    Private Sub Document_Open()
    If ActiveDocument.Characters.First.Text = vbTab Then
        With ActiveDocument
            With .Range.Find
                .Text = "^p^t"
                .Replacement.Text = "^p"
                .Execute Replace:=wdReplaceAll
                .MatchWildcards = True
                .Text = "^13Delete*^13"
                .Replacement.Text = "^p"
                .Execute Replace:=wdReplaceAll
            End With
        End With
    End If
    End Sub

    Open in new window

    Be aware that the macro will run when any document is opened. However it tries to detect the fact that it is an exported Access file by checking that the first character is a tab. If it is, it removes the tabs from the start of each paragraphs and then deletes all paragraphs starting with the word "Delete".

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now