Access to Word Formatting

Posted on 2014-08-18
Last Modified: 2014-08-19
My Access database has a Form (with a button on it) and a Report ('RptMaster').  When the button is clicked, I would like Access to Export the Report to an MS Word Document ('Word Catch').

Note:  I would only like information related to the Last Record Series worked on to be exported to ‘Word Catch.’

I would like the Word document to automatically perform the following functions:

1.      Open with the newly imported information
2.      Delete any paragraphs that begins with 'Delete—'
3.      Format the document so that all remaining paragraphs are shifted up—so that there are no huge gaps between each paragraph (I'd like the report to look orderly and organized).

For your reference, I've included a sample of the current Word document as it looks now—you can see how some paragraphs begin with 'Delete—' and you can also see how there are large gaps between some paragraphs.

Also, the Word Document is located at:  C:\Users\Matt\Desktop\World Catch.docx.rtf

Thanks for all of your help.
Question by:mdstalla
    LVL 76

    Accepted Solution

    As SouthMod points out, you have now closed the previous version of this question, You accepted my first attempt with a C grade, but ignored my second.
    The C grade imples that the question was not well answered, but you have not given any feedback.

    To treat this as a separate reques: firstly, it appears to be more of a project specification that a single question.

    a). You need to export part of a report. I suggest that you change that in the query on which the report is based.

    b). You need to Word to do something 'automatically'. If Word is not running, it cannot respond. Even if it is the number of events in Word is limited, However Word can be controlled in VBA from Access, which is why I suggested that you run the whole thing from Access.

    c) You need paragraphs beginning with 'Delete—'  to be deleted.

    d) You need spurious vertical space to be removed.

    Here is a rework of the second macro. As before it must be place in the ThisDocument module of your Normal template. Also as before, it runs whenever a document is opened, so it must look for something in the document to see if it is a target document. In your previous sample, each paragraph began with a tab character. This was used to identify and the document, but the code also remove thed the excess white space that it generated, and which I understood you wanted to remove.

    The tabs are absent your current sample document, so this version looks for some identifying text instead.
    Private Sub Document_Open()
        With ActiveDocument
            With .Range.Find
                'identify document
                .Text = "(XXCAINS)"
                If Not .Execute() Then
                    Exit Sub
                End If
                'delete flagged paragraphs
                .Text = "^13Delete--*^13"
                .Replacement.Text = "^p"
                .Forward = True
                .Wrap = wdFindContinue
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchAllWordForms = False
                .MatchSoundsLike = False
                .MatchWildcards = True
                .Execute Replace:=wdReplaceAll
                'delete paragraphs that only contain spaces
                .Text = "^13[ ]{1,}^13"
                .Replacement.Text = "^p"
                .Execute Replace:=wdReplaceAll
                'remove manual page breaks
                .MatchWildcards = False
                .Text = "^m"
                .Replacement.Text = ""
                .Execute Replace:=wdReplaceAll
            End With
            'remove inter-paragraph vertical space
            .Range.Paragraphs.SpaceAfter = 0
            .Range.Paragraphs.SpaceBefore = 0
        End With
    End Sub

    Open in new window


    Author Comment

    Hi Graham:

    When I place your code into the MS Word Macro VBA Screen and run it from that screen; it works.

    However, whenever I try to run the Macro from the document screen (I have a control button), I get an error message saying “The Macro Cannot Be found or is disabled Because of your Security Settings.”

    Please note that I have already reconfigured my Macro Settings (within the Trust Center) to “Enable All Macros.”

    Do you have any idea what I’m doing wrong or how to solove the problem?

    Author Closing Comment

    I’d like to can
    cel my last request to you— I did get your code to work, however, I've decided that exporting an Access Report to a Word Document is not the way to go.  The transfer of data is way too sloppy and unmanageable (even with the code that you gave me that reformats the text).  

    I'm going with Plan B:

    I'm going to simply create a Query in Access-- export that Query to Excel-- and then use that Excel Spreadsheet as Inserts into a pre-written MS Word Document.  This should make my Word document perfectly structured and organized… so that I can successfully perform the ‘Delete—‘function you were helping me out with.

    I will reach out to the general pool of Experts for how to make this happen.

    Thanks for your help.

    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

    This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
    Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

    728 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

    15 Experts available now in Live!

    Get 1:1 Help Now