Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automating hard coded numbers to cross reference (vba word 2003)

    My problem is: After a mail merge any cross referenced items have been stripped to hard text. The document is a legal agreement with cross references to various clauses, sections, etc. I was following another route to insert hyperlinks; however, when the source data file changes, it will change all agreements - not good! So here I am back at mail merge and now needing to write vba so that the user can click on a button to reinsert the cross references after the mail merge... (and I'm quit rusty with vba lately)!!

    So far, I have written code to search for any strings that include a space and number (0-9) and replace the space with a non-breaking space. Now my task is to find any keywords with a non-breaking space and number (i.e., article 7 or clause 3.2 or Schedule A) and search through the list of cross references for that number and replace the hard text number with the cross reference. I am using styles Headings 1-9 in the document with attached numbered lists. I have been researching this for a couple of days found different varities of the following code which does not seem to do anything. I'm not even sure this code is pointing in the right direction?

    Dim myHeadings
    Dim i
    Dim strNumberedHeading As String

    myHeadings = ActiveDocument.GetCrossReferenceItems(wdRefTypeHea ding)
    For i = 1 To UBound(myHeadings)
    If InStr(LCase$(myHeadings(i)), "article^s") Then
    Selection.InsertCrossReference _
    ReferenceType:=wdRefTypeHeading, _
    ReferenceKind:=wdPageNumber, ReferenceItem:=i
    Selection.InsertParagraphAfter
    End If
    Next i

    Any help would be muchly appreciated... thanx... trish

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating hard coded numbers to cross reference (vba word 2003)

    Somehow, I don't think this approach will work, at least not easily.

    Instead of using mail merge, I'd put placeholders in the text where items from the data source should be placed, e.g. [LastName].
    Then use code to loop through the records of the data source. For each record, create a new copy of the source document, then replace the placeholders with the values of the fields of the record.
    Save the document, then close it and move on to the next record.

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Automating hard coded numbers to cross reference (vba word 2003)

    Hi Trish,

    In your mailmerge template, aren't the cross-references pointing to particular paragraphs etc, for which the references get updated as needed during the mailmerge? In that case, why do you need to recreate them? Surely the fact that the hyperlinking is gone is of no consequence at this point.

    Be that as it may, if you really do need to recreate the cross-references, I'd suggest having two variables for each - a bookmark name and a cross-ref name that are inserted into your document as text instead of the actual bookmarked text and cross-references. Then, post merge, run your macro to replace the bookmark names with the required text, plus a bookmark name that includes an index number, and replace the corresponding cross-ref names with HYPERLINK fields (or REF or PAGEREF fields with a 'h' switch) pointing to the new indexed bookmark names. It should be fairly easy to manage this, since each merged letter will be in a new Section.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating hard coded numbers to cross reference (vba word 2003)

    As always, Hans, you point me in the right direction - I tend to get lost in the forest every once in a while... thanx for all your support throughout the years!

    When you refer to placeholder - do you mean something like a macrobutton field?

    Thanx so much... trish

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automating hard coded numbers to cross reference (vba word 2003)

    Thanx for your feedback... here's the process that I am trying to accomplish

    I created a report in BusinessObjects that feeds to an Excel spreadsheet via VBA. The merge is for a purchase/sale agreement approximately 40 pages long, using Words Headings 1-9 with numbering. Only one row of information is queried in BO and merged to this document. Through many of the clauses, cross-references are created in order to add/delete any new material. We found out the hard way that after the merge process, the new document strips the cross-references. Unfortunately, edits are still required to this document after-the-fact and even the information merged could potentially change throughout the life cycle of the agreement until a final is agreed upon.

    Your idea sounds very interesting; however, I think there is approx 150 cross-references in the document... I'm going think about this a little more... I'm sure you will here back from me in the near future...!!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automating hard coded numbers to cross reference (vba word 2003)

    Bu placeholder, I mean some kind of dummy text that be searched and replaced. For example, you could use the field name between square brackets: [LastName], or between @ characters: @LastName@. In your code, you would search for "[" & fieldname & "]" or for "@" & fieldname & "@" and replace it with the value of the field in the current record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •