Access to Word Formatting

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdstallaAuthor Commented:
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?
mdstallaAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.