Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating a Word Template wth ans SQL Table (2000)

    Hello,

    I am writing an application that needs to populates a Word Template with data from an SQL database.
    The WriteToWord.mdb seems to be a good example to start with. Does anyone know where to find it ?

    Thanks

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    How did you hear about that database? Is it a sample provided by Microsoft or someone else? Perhaps try Google and see if you can figure out where it comes from.

    As for other samples, I'm sure many of us can provide. May I assume that your post means that you want the code in a template but the data to be written into a new document based on that template (or just a blank document, as the case may be) rather than into the body of the template itself?

  3. #3
    New Lounger
    Join Date
    Sep 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    Well, that's it I browsed around on the net and saw a couple of references to this database. It might actually be harder to come by than I previously thought.

    You are right about what I want to do: create a new word.doc based on a template.dot where the Bookmarks are populated with Fields from SQL Tables. At this point, I can find and open the template no problem, retrieve a list of the Bookmarks. On the other hand I use ADO (this is done in Borland C++) objects to query the Tables.

    At this point I am not sure how to populate/replace the bookmarks... Any insight on this would be greatly appreciated.
    If I don't figure it out I guess I'll make a new post.

    Thanks for your answer
    Cheers

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    This might be review, but there are two approaches to inserting material into a document body: the more UI-oriented Selection object's methods, and the more abstract Range object's methods. The advantage of the Selection object is ease of development and debugging; the advantage of the Range object is speed and flexibility. Many methods apply to both, but it isn't always as easy to convert from one to the other as would be nice.

    Let me assume that your bookmarks are mere insertion point markers, and that you do not need to preserve anything already marked by the bookmark. In that case, you can do this:

    ActiveDocument.Bookmarks("myBookmark").Range.Inser tAfter objRS("FieldName").Value

    or this:

    ActiveDocument.Bookmarks("myBookmark").Select
    Selection.TypeText objRS("FieldName").Value

    Hope this helps.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    I wrote a couple of artilces last year that deal with automating Word from Access. The second part is opening a doc and populating bookmarks with data. It works with an already created document, but the same process applies to working with a document based on a template.

    You can read it, and download demo files from Word Automation from Access - Part II Inserting Text Into Word Bookmarks.

    The data should be able to come from linked SQL Tables as well as native Access Tables.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    New Lounger
    Join Date
    Sep 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    Thanks for the article.
    It IS helpful.

    I have an issue now with disappearing Bookmarks:

    Dim nBook As Integer
    nBook = oWord.ActiveDocument.Bookmarks.Count

    For i = 1 To nBook
    nBook = oWord.ActiveDocument.Bookmarks.Count
    oWord.ActiveDocument.Bookmarks(i).Select
    oWord.Selection.TypeText Text
    Next

    Inside the loop, nBook decrements from 11 to 10, 9,8 etc until it crashes half way...
    Like if by selecting and writing I delete the Bookmark !
    Any idea what's happening ?

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    > Like if by selecting and writing I delete the Bookmark !

    That is exactly what happens if you select a bookmark that is not merely an insertion point. I have looked for my past post on this, but I can't find it now.

    Okay, new code... Perhaps something like this:

    <pre>Dim nBook As Integer
    nBook = ActiveDocument.Bookmarks.Count

    For i = 1 To nBook
    With oWord.ActiveDocument.Bookmarks
    If .Item(i).Empty = True Then
    .Item(i).Select 'Select bookmark with no fear of overwriting
    Else
    ' Create another bookmark that covers the same text
    .Add Name:="bmkVeryTemporary", Range:=.Item(i).Range
    ' Collapse the real bookmark back to the beginning
    .Item(i).End = .Item(i).Start
    ' Select the new bookmark; the TypeText will kill this one
    ' and leave the real one at the beginning of the text
    .Item("bmkVeryTemporary").Select
    End If
    Selection.TypeText Text
    End With
    Next</pre>

    I tested without the oWord qualifier. I also deleted the nWord assignment inside the loop. That seems fairly dangerous. Anyway, if you step through the macro watching how it handles the bookmarks, I think you will find this preserves the bookmark while replacing whatever it contained. Hope this helps.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating a Word Template wth ans SQL Table (2000)

    <hr>Like if by selecting and writing I delete the Bookmark !
    Any idea what's happening ?<hr>
    That is exactly what is happening, assuming that the bookmarks are bookmarks with text in them.

    To "fix" it, you should either make the bookmarks, just bookmarks with no text in them, or select the text you just added and reinsert the bookmark.

    Something like this should do the job:

    <pre>nBook = ActiveDocument.Bookmarks.Count

    For i = 1 To nBook
    strbmkName = Bookmarks(i).Name
    ActiveDocument.Bookmarks(i).Select
    strTextTOAdd = "Text " & CStr(i)
    Selection.TypeText strTextTOAdd
    Selection.MoveRight wdCharacter, -Len(strTextTOAdd), True
    Bookmarks.Add strbmkName, Selection.Range
    Next</pre>

    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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