macro/vba to search, analyze, and act in a merge 1

[CMTHOMAS]

Hello,
I have a Word2002 .dot I am creating that will create a letter using mail merge (source is an excel file). I have created portions of the letter (I'll call them paragraphs) that include if/then fields (not macros) to display text dependent on the contents of the field. I created bookmarks around each paragraph. I need to have paragraphs deleted if the record does not include that data type. For example:
Of the services we provide, client1 only engaged us to complete task 1, 5, and 7 (we put a y in those columns) so the bookmarks 2, 3, and 6 need to be deleted.
Each time we use the merge, we will be merging 100 or more clients, so we do not want to do this manually. I am just beginning using VBA (just bought the Absolute Beginner's Guide to VBA), so be gentle...
Any help is appreciated.

 

[Bong]

I'm a beginner also so I'll wait and see if the real experts dip an oar in. Meanwhile, it seems to me that this application would be better suited to Access; you wouldn't even need VBA, I don't think. Why are you using Word?

_________________
Bob Rashkin

 

[CMTHOMAS]

Thanks.
Of course, that would be the easy way, but my company is not licensed for Access. Word, Excel, PowerPoint and Outlook only. I have to work with what I've got.

 

[Bong]

OK. What have you tried so far?

_________________
Bob Rashkin

 

[CMTHOMAS]

I really don't know where to start. I tried creating an if/then field to place the text DELETE BOOKMARK within the bookmark to be removed. I then tried a CASE ELSE vba sub to find and delete the so marked bookmarks. I couldn't get the syntax right. I just don't know enough about the options to try anything...

 

[Bong]

Just today, I found this in a previous thread:
ActiveDocument.Bookmarks("<bookmark name>").Range.Delete

I think you want to step through your tags (fields) and
if <field pointer>.value = "y" then
<do your thing>
else
ActiveDocument.Bookmarks("<bookmark name>").Range.Delete
end if

or something like that.

_________________
Bob Rashkin

 

[CMTHOMAS]

Thanks, I'll give that a try. Just in case, can you give me a link to the previous thread?

Colleen Thomas

 

[Bong]

thread707-1255694


_________________
Bob Rashkin

 

[CMTHOMAS]

So lost. I started with your hint, and I have tried a dozen things since. I (briefly) had it working using in the template displaying a single record, but upon merging, the bookmarks disappear, and it doesn't work anymore. Have any hints as to where to begin with this?

 

[abaldwin]

Even though you previously stated

Of course, that would be the easy way, but my company is not licensed for Access. Word, Excel, PowerPoint and Outlook only. I have to work with what I've got.

I fear you may not want to hear this but.......... This is really where the Access DB is really where you need to be. This would be simply a report in Access that you can probably do relatively easily. You are being asked to do something with Word (the wrong application) and you have probably spent more time in you failures than you would if this were a report.

Just my $.02

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[Bong]

I agree with abaldwin but as you've stated that Access is out, I'm willing to participate in this folly.

I think what might be happening is that you are deleting the bookmarks along with the text/paragraphs they reference when the code isn't "y". What you need to do is keep the template isolated from this, either by working from a copy or by saving the template off before the merge.

Maybe?

_________________
Bob Rashkin

 

[CMTHOMAS]

Because each client record is different, I can't run the "clean-up" portion of the code (macro) until after I have run the merge into a new document. This removes the bookmark (because we can't have 100+ bookmark(1)s in a document), and that gets rid of my reference points for the bookmark delete portion of the macro. I tried recording a macro for each bookmark-delete, and writing a code that searches the document for certain text "delete this section", which is determined by an if/then field at the end of the appropriate text. The problem is, since there are several mergefields within each text area, the sections aren't the same size each time, so my "delete the previous X number of lines" in my prerecorded macro doesn't always delete the right amount of text. (See code below)

Sub delete_service_sections()
'
' delete_service_sections Macro
' Macro recorded 7/21/2006 by cthomas
    'search for and delete payroll_deposits bookmark
    Selection.HomeKey Unit:=wdStory
    Selection.Find.Execute
    With Selection.Find
        .Text = "DELETE payroll_deposits"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    ActiveWindow.ActivePane.VerticalPercentScrolled = 7
    Selection.MoveUp Unit:=wdParagraph, Count:=7, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    
    'search for and delete sales_tax bookmark
    Selection.HomeKey Unit:=wdStory
    Selection.Find.Execute
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "DELETE sales_tax"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    ActiveWindow.ActivePane.VerticalPercentScrolled = 8
    Selection.MoveUp Unit:=wdParagraph, Count:=8, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    
'etc., etc., for each client task type...
End Sub

I would have to loop that until reaching the end of the document.

ALSO, I TRIED THIS USING THE "Y" in the field...

Sub filterOutUnneededSections()
' Macro recorded 7/21/2006 by cthomas
    Dim checkThisSection As Integer
    Dim TextFromSection As String
    Dim YesOrNo As String
    For Each Section In ActiveDocument
        checkThisSection = 2
        ' to remove sections of the engagment letter that do not apply to the client
        ActiveDocument.Sections(checkThisSection).Range.Select
        TextFromSection = Selection.Range
        Trim$ (TextFromSection)
        YesOrNo = Right$(TextFromSection, 1)
            If YesOrNo = "y" Then
            'some statement to replace the "y" text with a blank line
            Else: ActiveDocument.Section(checkThisSection).Range.Delete
            End If
        checkThisSection = checkThisSection + 1
    Next Section
End Sub

My syntax is wrong, so that isn't working either...

Thanks guys!

 

[CMTHOMAS]

SOLVED IT!
I created the following macro to run after the merge. I had to insert a continuous section break after each service section, as the bookmarks didn't get inserted in the merge. Thanks for the help, I really appreciate your efforts!

Sub abba()
'
' abba Macro
' Macro recorded 7/28/2006 by cthomas
'
    Dim totalSections As Integer
    Dim strTemp As String
    Dim sectionCount As Integer
    'Count the sections in the document and save result
    totalSections = ActiveDocument.Sections.Count
    MsgBox ActiveDocument.Sections.Count & " sections"
    sectionCount = 1
    'Set up loop to check each section
    For x = 1 To totalSections
        'Select text at bottom of section to check for whether or not _
        those services are provided by business services group
        Application.Browser.Target = wdBrowseSection
        Application.Browser.Next
        Selection.MoveUp Unit:=wdLine, Count:=3
        Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
        strTemp = Selection.Text
        'Delete the carriage return following text, if necessary
        If Right(strTemp, 1) = vbCr Then _
            strTemp = Left(strTemp, Len(strTemp) - 1)
        'Delete the yes text if that service is provided
        If strTemp = "yes" Then _
            Selection.Delete: Selection.MoveDown Unit:=wdLine, Count:=3
        'Delete the section if that service is not provided
        If strTemp = "del" Then _
            ActiveDocument.Sections(sectionCount).Range.Select: Selection.Delete: _
                sectionCount = sectionCount - 1: totalSections = totalSections - 1
        'Move to next section if selected section is not a service-related section
        If strTemp <> "del" And strTemp <> "yes" Then _
                Selection.MoveDown Unit:=wdLine, Count:=3
        sectionCount = sectionCount + 1
    'Loop back to top of For statement
    Next x
    
End Sub

 

[Chance1234]

Continuing on the book marks take a look at this faq707-6139

could be altered around so something like

If XXX = whateveryourtestingmaybe then
strbk1 = "Whatever text will be"
else
strbk1 = "or it could be this text
end if


Chance,

Filmmaker, gentleman and Legal champion of the small people.

 

[fumei]

Could you describe:

1. what exactly is bookmarked, and, are these bookmarks location bookmarks (that is, there is no text, it is a location), or do the bookmarks contain text?

2. How are the bookmarks populated with content? Is is with Bookmarks("whatever").Range.Text = blahblah? Because, yes, that will delete the bookmark itself. There are ways around this.

3. The text at the end of the sections, how is that getting in there. I am pretty sure there are better ways to do this than messing with all that Selection.Move stuff.

Gerry
My paintings and sculpture