[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access to Word Formatting

Posted on 2014-08-18
Medium Priority
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
  • 2
LVL 76

Accepted Solution

GrahamSkan earned 2000 total points
ID: 40269894
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

ID: 40270949
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

ID: 40271352
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

825 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