asked on

MS Word Macro Code

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
Report1.rtf

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mdstalla

ASKER

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!

GrahamSkan

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
        .Characters.First.Delete
        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".