by John Bevilaqua - DLL Source License Available for Sale (1 Submission)
Category: OLE/COM/DCOM/Active-X
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 16th March 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Fully automates a 3-tiered Word for Windows Mail Merge from a SQL Server Database using a parameterized SQL Query through an XML Data Island
VBcoders
<HTML>
<HEAD>
<OBJECT ID="Automateword"
CLASSID="CLSID:32646EBA-0919-4C2F-94D6-599F46DC34F2"
CODEBASE="http://www.xxxxxxxxxx.com/customer/package/AutomateWord.CAB#version=1,0,0,0">
</OBJECT>
<title>Enter Customer ID</title>
</HEAD>
<BODY>
<%
' Option Explicit
On Error Resume Next
%>
<FORM ACTION="RUNSQL2.ASP" method="post">
<font size=5 style=Helvetica>21st Century, Inc. Microsoft Word Mail Merge using an ActiveX DLL against a SQL Database via an XML Data Island<br>
<font size=3>For More Info Call John Bevilaqua at 401-231-6604 or Send us an eMail:<a href="mailto:[email protected]">Email Us</a>
<!-- Transfer to Home Page<a href="http://www.21stCenturyeCommerce.net">Home Page</a> -->
This is a demonstration of how an Automated Word Mail Merge into pre-defined Bookmarks can be
achieved using an ActiveX DLL Control run from a remote ASP Web Application which extracts the
data from a remote SQL Server Database, converts it into an XML Data Island and then performs
the Word Mail Merge, presents the Merged Document to the user and requests a local or network
sub-directory browse list if the user indicates they want to save the document. An option to
Upload the Merged Document to a Web Directory could also be implemented using SA File-Up or
ASP Upload, or any other equivalent 3rd Party Vendor using their licensed and installed DLLs.
This current implementation assumes that the complex Word Doc Template for the project resides in a Web
Directory where both the .DOC file is Read-Only and the merged document is protected from updates
or changes after the merge has occurred in case the document produced is a price quotation.
This application obtains data from a single table but Parent-Child relationships could be supported
and implemented for an additonal development investment. It would be possible to support multiple
Word Doc Templates from a single Data Extraction Project going against the same SQL Table with
additional drop-down picklist input for the Template Name as an add-on feature. <br><br>
Input a Valid Northwind Customer Code like "BLAUS", "FRANK", "OCEAN", "PARIS", "RANCH", "CACTU"
<P/><INPUT TYPE="TEXT" NAME="SQL" VALUE=<%=Request.Form("SQL")%> >
<!-- <P/><INPUT TYPE="TEXT" NAME="SQL" VALUE="BLAUS"> -->
<P/><BUTTON ID="InvoiceButton">Create DOCs</BUTTON> This option retrieves data then performs the Mail Merge using an XML Data Island in 3-7 seconds.<br>
<Input Type="Submit">This button merely performs the SQL Data Query Retrieval to produce formatted HTML Recordset Displays
</FORM>
<a href="http://aspwire.com/" target="_top">
<img src="http://www.xxxxxxxxxxxx.com/customer/ASPWire.gif" border=0
alt="ASP News & Information Source"></a>
<%
If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
Dim oConn,oRS,strConn,sSQLServer,sSQLQuery
' strConn = "Provider=Microsoft Jet Engine;Persist Security Info=False;" & _
' "User ID=sa;Initial Catalog=customers;Data Source=" & sSQLServer
Response.ContentType = "text/xml"
' changed back to text/xml data
' Response.ContentType = "text/html"
' Create a connection.
set oConn = Server.CreateObject("ADODB.Connection")
' Open the connection.
oConn.Open strConn
' Execute the SQL statement.
strSQLQuery = "Select * from CUSTOMERS where CustomerID='" & Request.Form("SQL") & "'"
Response.Write("Executing: " & strSQLQuery & "<br>")
' DO NOT FORGET TO SEND PARM AS QUERY STRING set oRS = oConn.Execute(Request.QueryString(strSQLQuery))
set oRS = oConn.Execute(strSQLQuery)
' Save the recordset in the Response object.
oRS.Save Response,1
If Err.Number <> 0 Then
Response.Write "An error has occurred!<br>"
Response.Write "Error number: " & Err.number & "<br>"
Response.Write "Error description: " & Err.description & "<br>"
ElseIf oRS.EOF Then
Response.Write "Empty recordset returned."
Else
iFields = oRS.Fields.Count
Response.Write "<table border=1><tr>"
For i = 0 To iFields - 1
Response.Write "<td><b>" & oRS.Fields(i).Name & "</b></td>"
Next
Response.Write "</tr>"
While Not oRS.EOF
Response.Write "<tr>"
For i = 0 To iFields - 1
Response.Write "<td>" & oRS.Fields(i).Value & "</td>"
Next
Response.Write "</tr>"
oRS.MoveNext
Wend
End If
' You MUST move to top of record set so the first record is loaded into XML Data Island
oRS.MoveFirst
End If
%>
<XML ID="DataXML">
<%Response.Write "<Order>"%>
<%Response.Write "<CustName>" & oRS("ContactName") & "</CustName>" %>
<%Response.Write "<CustAddr>" & oRS("Address") & "</CustAddr>" %>
<%Response.Write "<CustCityZip>" & oRS("City") & "</CustCityZip>"%>
<%Response.Write "<CustPhone>" & oRS("Phone") & "</CustPhone>" %>
<%Response.Write "<OrderID>10598</OrderID>" %>
<%Response.Write "<OrderDate>1/31/2002</OrderDate>" %>
<%Response.Write "<CustID>" & oRS("CompanyName") & "</CustID>" %>
<%Response.Write "<CustInfo>" & oRS("Address") & " " & oRS("City") & " " & oRS("Region") & " " & oRS("PostalCode") & " " & oRS("Country") & "</CustInfo>"%>
<Items>
<Product Desc="ActiveX Controls" Qty="5" Price="79.50" Disc="0.2"/>
<Product Desc="OLE DB Linking" Qty="12" Price="99.95" Disc="0.2"/>
<Product Desc="Mail Merge Documents" Qty="80" Price="125.00" Disc="0.2"/>
<Product Desc="XML Return Objects" Qty="600" Price="1.50" Disc="0.2"/>
</Items>
</Order>
</XML>
</BODY>
<SCRIPT Language="VBScript">
Function InvoiceButton_OnClick()
AutoWord.SendData DataXML.XMLDocument
AutoWord.MakeInvoice "http://www.xxxxxxxxxxxx.com/customer/invoice.doc"
End Function
</SCRIPT>
</HTML>
No comments have been posted about Fully automates a 3-tiered Word for Windows Mail Merge from a SQL Server Database using a parameter. Why not be the first to post a comment about Fully automates a 3-tiered Word for Windows Mail Merge from a SQL Server Database using a parameter.
