Read HTML formatted data from database field, Insert Wordmerge fields, and save as Word document 
Author Message
 Read HTML formatted data from database field, Insert Wordmerge fields, and save as Word document

   What I'm trying to do is to read a reasonably large amount of text (a
letter) that has been stored in HTML format in a database field.  The
ultimate Wordmerge areas in that text will be marked at this point as
<<FirstName>>, <<Address1>>, <<FirstName>> perhaps again, etc.  I need to
read the HTML formatted text from the database field, replace the pseudo
WordMerge specifications with real WordMerge commands so that later
substitution of data can occur, and then save the document as a standard
Word document with of course the WordMerge fields residing in the
appropriate locations within the letter.  My particular question is instead
of using the sql resultset to determine whether a Find will succeed, how can
I determine whether a Find worked programmatically (i.e. I know the
potential extents of WordMerge areas that may ultimately need to be done,
but each letter may have a different combination, thus I can't expect a
specific WordMerge field to be needed for a particular letter)?  Oh, and if
anything else looks goofy(scientific term), let me know. :-)
.
.
.
    Dim wrdApp As Word.Application
   {assume that smailmergefields is a prepopulated array with all the
potential WordMerge fields for a particular document}
    'perform query to obtain letter of current interest
    sSql = "SELECT txtLetterContent FROM ...
    Set rSql = oCon.Execute(sSql)
    'open up skeletal document that only contains one bookmark called Letter
(marks the location where entire letter will be placed)
    wrdApp.Documents.Open FileName:="C:\Docs\skeletal.doc",
Format:=wdOpenFormatHTML
    wrdApp.Selection.GoTo What:=wdGoToBookmark, Name:="Letter"
    'store entire letter in Word document as obtained from the database
field
    wrdApp.Selection.TypeText    rSql!txtLetterContent
    istart = 1
    'Loop through all potential placeholder phrases and replace dummy mail
merge syntax with the actual mail merge commands
    For i = 0 To UBound(smailmergefields) - 1
        With wrdApp.Selection.Find
             .Wrap = wdFindContinue: .Forward = True
             sdummymailmerge = "<<" & smailmergefields(i) & ">>"
             bkeeplooking = True
             'continue looking for a specific placeholder phrase until it is
no longer found (may be multiple instances, e.g. 3 instances of
<<FirstName>>)
             While bkeeplooking
                   'determine ahead of time whether a Find command will
actually locate this sequence of characters when I try it next
                   ifoundat = InStr(istart, rSql!txtLetterContent,
sdummymailmerge)
                   If ifoundat Then
                      'go to dummy phrase, delete phrase, add mailmerge
field at cursor location, then move forward a little so we don't find same
thing again
                      'of particular interest here would be a way to know if
I had found the text searched for instead of doing the above InStr first to
know that
                      'and also to delete the dummywordmerge with one fell
swoop instead of character by character
                      .Execute FindText:=sdummymailmerge
                      Selection.Delete Unit:=wdCharacter,
Count:=Len(sdummymailmerge)
                      ActiveDocument.MailMerge.Fields.Add
Range:=Selection.Range, Name:=smailmergefields(i)
                      istart = ifoundat + 2
                   Else
                      bkeeplooking = False
                   End If
             Wend
         End With
    Next i
    'now that document has had pseudo WordMerge fields replaced with real
ones, convert the document via Save from HTML to Word format
    wrdApp.Document.SaveAs Filename:="Myfile.doc",
FileFormat:=wdFormatDocument,...
    .
    .
    .

Thanks, Tom.



Mon, 17 Oct 2005 21:31:47 GMT  
 Read HTML formatted data from database field, Insert Wordmerge fields, and save as Word document
Hi Tom,

If I understand your question, you can use:

Do While .Execute(FindText: = "<<" & smailmergefields(i) & ">>"}",
Wrap:=wdFindContinue, Forward:=True) = True
    Selection.Delete
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range,
Name:=smailmergefields(i)
Loop

Please respond to the newsgroups for the benefit of others who may be
interested.

Hope this helps
Doug Robbins - Word MVP

Quote:
>    What I'm trying to do is to read a reasonably large amount of text (a
> letter) that has been stored in HTML format in a database field.  The
> ultimate Wordmerge areas in that text will be marked at this point as
> <<FirstName>>, <<Address1>>, <<FirstName>> perhaps again, etc.  I need to
> read the HTML formatted text from the database field, replace the pseudo
> WordMerge specifications with real WordMerge commands so that later
> substitution of data can occur, and then save the document as a standard
> Word document with of course the WordMerge fields residing in the
> appropriate locations within the letter.  My particular question is
instead
> of using the sql resultset to determine whether a Find will succeed, how
can
> I determine whether a Find worked programmatically (i.e. I know the
> potential extents of WordMerge areas that may ultimately need to be done,
> but each letter may have a different combination, thus I can't expect a
> specific WordMerge field to be needed for a particular letter)?  Oh, and
if
> anything else looks goofy(scientific term), let me know. :-)
> .
> .
> .
>     Dim wrdApp As Word.Application
>    {assume that smailmergefields is a prepopulated array with all the
> potential WordMerge fields for a particular document}
>     'perform query to obtain letter of current interest
>     sSql = "SELECT txtLetterContent FROM ...
>     Set rSql = oCon.Execute(sSql)
>     'open up skeletal document that only contains one bookmark called
Letter
> (marks the location where entire letter will be placed)
>     wrdApp.Documents.Open FileName:="C:\Docs\skeletal.doc",
> Format:=wdOpenFormatHTML
>     wrdApp.Selection.GoTo What:=wdGoToBookmark, Name:="Letter"
>     'store entire letter in Word document as obtained from the database
> field
>     wrdApp.Selection.TypeText    rSql!txtLetterContent
>     istart = 1
>     'Loop through all potential placeholder phrases and replace dummy mail
> merge syntax with the actual mail merge commands
>     For i = 0 To UBound(smailmergefields) - 1
>         With wrdApp.Selection.Find
>              .Wrap = wdFindContinue: .Forward = True
>              sdummymailmerge = "<<" & smailmergefields(i) & ">>"
>              bkeeplooking = True
>              'continue looking for a specific placeholder phrase until it
is
> no longer found (may be multiple instances, e.g. 3 instances of
> <<FirstName>>)
>              While bkeeplooking
>                    'determine ahead of time whether a Find command will
> actually locate this sequence of characters when I try it next
>                    ifoundat = InStr(istart, rSql!txtLetterContent,
> sdummymailmerge)
>                    If ifoundat Then
>                       'go to dummy phrase, delete phrase, add mailmerge
> field at cursor location, then move forward a little so we don't find same
> thing again
>                       'of particular interest here would be a way to know
if
> I had found the text searched for instead of doing the above InStr first
to
> know that
>                       'and also to delete the dummywordmerge with one fell
> swoop instead of character by character
>                       .Execute FindText:=sdummymailmerge
>                       Selection.Delete Unit:=wdCharacter,
> Count:=Len(sdummymailmerge)
>                       ActiveDocument.MailMerge.Fields.Add
> Range:=Selection.Range, Name:=smailmergefields(i)
>                       istart = ifoundat + 2
>                    Else
>                       bkeeplooking = False
>                    End If
>              Wend
>          End With
>     Next i
>     'now that document has had pseudo WordMerge fields replaced with real
> ones, convert the document via Save from HTML to Word format
>     wrdApp.Document.SaveAs Filename:="Myfile.doc",
> FileFormat:=wdFormatDocument,...
>     .
>     .
>     .

> Thanks, Tom.



Tue, 18 Oct 2005 06:49:31 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Problem with format on insert autotext, and in inserting elements with form fields

2. Help reading/formatting date field from DAO database?

3. Reading a field and using that field to place a number in another field

4. Inserting Data into LONG data type field in an ORACLE Table

5. Inserting html into OLE field ?

6. inserting html code into access memo-field

7. VBA - Format problem inserting high numeric value into text field in excel

8. Inserting multiple fields into a letter format Crystal Report

9. save from VB picturebox to SQL image field using INSERT INTO

10. How to preserve formating when saving to HTML and field codes

11. How to preserve formating when saving to HTML and field codes

12. bound masked field to date/time field in access not formatting right

 

 
Powered by phpBB® Forum Software