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
mdstallaAsked:
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.

GrahamSkanRetiredCommented:
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

0

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:
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!
0
GrahamSkanRetiredCommented:
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".
0
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 Office

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.