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


MS Word Macro Code

Posted on 2014-08-11
Medium Priority
Last Modified: 2014-08-18
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
Question by:mdstalla
  • 2
LVL 76

Accepted Solution

GrahamSkan earned 1000 total points
ID: 40255059
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


Author Comment

ID: 40257592
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!
LVL 76

Expert Comment

ID: 40257895
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
        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".

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

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