Word 2002 Mail Merge with HTML file

[c_shah]

I am using VB.net (currently, I am able to connect to SQL Server
tables using open data source)..

can I use WordObj.MAilMerge.OpenDataSource to connect to my HTML file?

 

[Peter Jamieson]

I think you should be able to unless Word 2002 differs from Word 2003 in
this respect. However, what happens depends on how the data is structured
within the HTML file.

Assuming WordObj actually references a Word.Document object, have you tried
the VB.NET equivalent of


WordObj.MailMerge.OpenDataSource _
Name:="the path name of your HTML file"

or maybe

WordObj.MailMerge.OpenDataSource _
Name:="the path name of your HTML file", _
Subtype:=wdMergeSubtypeOther

?
If the HTML file just contains a single table, you should be OK (the data
source is treated rather like a Word document containing a table). however,
if the HTML file contains something more like a comma-delimited or
tab-delimited source, you will probably see a dialog asking what the
delimters should be. In this case, the data source is treated more like a
..txt file opened using Word's .txt file converter).

Peter Jamieson

 

[c_shah]

Peter it works..only thing is I am getting a select table dialog box..

Dim WordApp As New Word.Application
Dim WordDoc As New Word.Document

Try
WordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone
WordApp.Visible = True 'Make Word Visible
WordDoc = WordApp.Documents.Open("C:\MailMerge\Forms
\CANCELLATION_LETTER.doc")


WordDoc.MailMerge.OpenDataSource(Name:= _
"C:\MailMerge\Forms
\CANC_LETTER.html" _
,
SubType:=Word.WdMergeSubType.wdMergeSubTypeWord2000)

' the above line ask select a table, HTML code looks fine to me

WordDoc.MailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument

' suppress blank lines when mail merge fields in a mail merge main
document are empty
'WordDoc.MailMerge.SuppressBlankLines = True
WordApp.ActiveDocument.MailMerge.SuppressBlankLines = True

'Perform Merge
WordDoc.MailMerge.Execute()

WordApp.ActiveDocument.SaveAs("C:\MailMerge\Forms\test.doc")

WordApp.ActiveDocument.Close()
WordDoc.Close(False)

Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
WordDoc = Nothing
WordApp.Quit(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(WordApp)
WordApp = Nothing
End Try

 

[Peter Jamieson]

OK, this is not very familiar ground for me, but...

When the Select table dialog pops up, note the table name (let's say it is
'xyz')

Then try:

WordDoc.MailMerge.OpenDataSource( _
Name:="C:\MailMerge\Forms\CANC_LETTER.html", _
SQLStatement:="SELECT * FROM `abc`", _
SubType:=Word.WdMergeSubType.wdMergeSubTypeAccess)

I leave you to get the syntax right, but you may find that you can use [abc]
instead of `abc`

This suggests that Word 2002 connects by default using the Jet OLE DB driver
and its HTML engine, and that even if there is only one table in the HTML
file, you will still be prompted if you do not supply the SQL that selects
the table - I think that's different from the way Word 2003 does it, but
there may be some other difference I am not aware of. It surprises me that
using the wdMergeSubTypeWord2000 option does not work, but there it is.

There are some rules about how the OLE DB provider determines the names of
tables inside an HTML document - I'll try to dig them out.

Peter Jamieson

 

[c_shah]

Peter, everything is working now....i recorded a macro and used the
connection string recorded by the macro...
found somewhere in this newsgroup a post from Cindy M...

thank you......

 

[c_shah]

You are right....table name that's what I needed..when I recorded a
macro it had that table name as in sql statement

SQLStatement:="SELECT * FROM `Table`"

 

[c_shah]

Peter, if you are reading this...

I would appreciate if you could answer my question..is there a
limitation on number of merge fields (if you use HTML file as your
datasource)?

 

[Peter Jamieson]

Sorry, I don't know for certain and am not in a position to check right now,
but
a. if Word is using its native facility to open HTML files, which it
probably is when it reads an HTML table and you aren't using OLE DB, I would
guess the limit is the same as the Word table column count maximum - 63 or
64
b. if Word is using the text converter, the limit is likely to be "a large
number - perhaps well over 500)
c. if Word is using OLE DB, which it is when you specify
Word.WdMergeSubType.wdMergeSubTypeAccess, the limit is /likely/ to be either
127 or 255.

Peter Jamieson

 

[c_shah]

Thanks!