Help Please

• Gilles Lafreniere

  Thank you so much!

  The original code author fixed the problem for me, but I will read through this and perhaps find it better - and now something I can do myself.

  :)

  Laura

  
  
  

• Aleniko29139

   

  Hi Laura,

  I've been through this with mail merge and found much better ways to do it.

  I'm going to post and article from the Knowledge base and the rason I'm posting it is that the original article wasn't converted to Vs2005 and I think i copied my updates into this:  BEWARE of linewrap syntax errors:

  What I did was to goo from Access to XML to RTF. It worked really beautifuly and I'd never consider doing anything in mail merge after doing this.

   

   

   

  How to use ASP.NET or Visual Basic .NET to transform XML to Rich Text Format for Microsoft Word 2002

  Use this step-by-step guide to generate Rich Text Format (RTF) from Extensible Markup Language (XML) by using Visual Basic .NET.
  
  Rich Text Format is a text-based format that encodes formatted text, document layout, and graphics. It is commonly used with Microsoft Word. Because RTF is text-based, it can be easily generated with code. If you have XML data that you want to display in Word as a catalog/list or mail merge type document, then transforming your XML data into an RTF stream might be an ideal solution for you. And, in fact, if you are developing a solution where you generate documents on a Web server, then generating those documents using a text-based format, such as HTML or RTF, is preferred over using server-side Automation of Word.
  
  This article provides sample code, with step-by-step instructions, to transform XML into RTF for display in Word by using several approaches:

  ?	Save the RTF to a file and open it in Word.
  ?	Transfer the RTF to Word by using the Windows Clipboard.
  ?	Stream the RTF to Word hosted in Microsoft Internet Explorer from an ASP.NET Web Application.

  Back to the top

  The Rich Text Format Specification

  The Rich Text Format (RTF) specification is a public specification to generate RTF-compatible text files. You can use the documentation for the specification at the following Microsoft Developer Network (MSDN) Web site as a resource to help you build your own RTF files. However, the specification is provided "as-is", and no support is provided by Microsoft Technical Support for the specification. Click the following links for the RTF Specifications:

  Rich Text Format Specification, version 1.6 (http://msdn.microsoft.com/library/default.asp URL=/library/specs/rtfspec.htm)
  
  Rich Text Format Specification, version 1.7 (http://msdn.microsoft.com/downloads/default.asp URL=/downloads/sample.asp url=/MSDN-FILES/027/001/758/msdncompositedoc.xml)

  Back to the top

  Transform XML to RTF

  You can take valid XML from any source and transform it to RTF. The following procedure illustrates how you can transform custom XML into RTF either saved to a file or copied to the clipboard.

  1.	Create a new Visual Basic .NET Windows Application. Form1 is created for you.
  2.	Add two button controls to Form1.
  3.	On the View menu, click Code.
  4.	Insert the following at the top of the code window before the Form1 Class implementation: Imports System.Xml Imports System.Xml.Xsl Imports System.IO
  5.	Add the following code to the Form1 Class implementation (before End Class):
  6.	Add an XML file to your project: a. On the Project menu, click Add New Item. b. From the list of templates, click XML File. c. Type the name Dictionary.xml and then click Open. d. Append the following to the contents of Dictionary.xml: <Dictionary> <Entries> <Entry> <Word Type="1">Energetic</Word> <Definition>Having, exerting, or displaying energy</Definition> </Entry> <Entry> <Word Type="1">Happy</Word> <Definition>Enjoying, displaying, or characterized by pleasure or joy</Definition> </Entry> <Entry> <Word Type="2">Emotion</Word> <Definition>A complex, strong subjective response</Definition> </Entry> </Entries> </Dictionary>
  7.	Add an XSLT file to your project: a. On the Project menu, click Add New Item. b. From the list of templates, click XSLT File. c. Type the name Dictionary.xslt and then click Open. d. Replace the contents of Dictionary.xslt with the following: < xml version="1.0" encoding="UTF-8" > <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="text"/> <xsl:template match="/"> <xsl:text>{\rtf1</xsl:text> <xsl:for-each select="Dictionary/Entries/Entry"> <xsl:text>\par\b </xsl:text> <xsl:value-of select="Word"/> <xsl:text>\b0\i </xsl:text> <xsl:if test="Word[@Type='1']">adj.</xsl:if> <xsl:if test="Word[@Type='2']">n.</xsl:if> <xsl:text>\i0\par </xsl:text> <xsl:value-of select="Definition"/> <xsl:text>\par</xsl:text> </xsl:for-each> <xsl:text>}</xsl:text> </xsl:template> </xsl:stylesheet>
  8.	Press F5 to build and run the program.
  9.	Click To File to save the transformed XML to a file (Dictionary.rtf). You can open the RTF file in Word to examine the results of the transformation.
  10.	Click To Clipboard to copy the transformed XML to the Windows clipboard. You can then paste the clipboard contents into a new or existing Word document to see the results.

  Back to the top

  Transform a DataSet Into RTF

  Visual Basic .NET allows to you to easily do transformations on datasets. This procedure shows you how you can take related data from the sample Northwind database and transform it to RTF. Two different transformations are demonstrated: a simple RTF document that lists Customer contact information and a somewhat more complex RTF document that displays order information for customers in a mail merge-type format.

  1.	Start a new Visual Basic ASP.NET Web Application and save it to http://localhost/RTFDemo. WebForm1 is created for you.
  2.	Add two Button controls to WebForm1.
  3.	On the View menu, click Code.
  4.	Add the following code to the Page_Load function: Button1.Text = "View Contact Information" Button2.Text = "View Customer Orders"
  5.	Add the following function to the WebForm1 class. NOTE: The following code assumes you have SQL Server installed on the localhost. If you have to use another computer, change the Data Source member of the connection string accordingly. Private Sub ButtonsClick(ByVal sender As System.Object, ByVal e As System.EventArgs) _                 Handles Button1.Click, Button2.Click         Dim nwindConn As SqlConnection = New SqlConnection( _                  "Server=.\MHASQLE;Initial Catalog=NorthwindCS;User ID=User; Password=Password; Trusted_Connection=true;")         nwindConn.Open()         Dim SPath As String = Application.StartupPath.Substring(0, Application.StartupPath.LastIndexOf("\"))         SPath = SPath.Substring(0, SPath.LastIndexOf("\") + 1)         ' Build a dataset based on whether you requested to view a list of         ' orders or a list of contacts.         Dim ds As DataSet = Nothing         Dim sXSL As String = "" : Dim Fname As String = ""         Select Case (sender.name)               Case "Button1"                   ds = New DataSet("Contacts")                 Dim ContactsDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", nwindConn)                 ContactsDA.Fill(ds, "Customers")                 ' XSLT to use for transforming this dataset.                 sXSL = "Contacts.xslt"                 Fname = "Contacts.Doc"               Case "Button2"                   ds = New DataSet("CustomerOrders")                   Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName, " & _                     "Address, City, Region, PostalCode, Country FROM Customers", nwindConn)                 custDA.Fill(ds, "Customers")                   Dim ordersDA As SqlDataAdapter = New SqlDataAdapter("SELECT OrderID, CustomerID, Freight " & _                     "FROM Orders", nwindConn)                 ordersDA.Fill(ds, "Orders")                   Dim ordersdetailDA As SqlDataAdapter = New SqlDataAdapter( _                     "SELECT [Order Details].OrderID, Products.ProductName, [Order Details].Quantity, " & _                     "[Order Details].[UnitPrice]*[Quantity]*(1-[Discount]) AS ItemTotal " & _                     "FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID " _                     , nwindConn)                   ordersdetailDA.Fill(ds, "OrderDetails")                   nwindConn.Close()                   ds.Relations.Add("CustOrders", _                 ds.Tables("Customers").Columns("CustomerID"), _                 ds.Tables("Orders").Columns("CustomerID")).Nested = True                   ds.Relations.Add("OrdersToOrdersDetail", _                 ds.Tables("Orders").Columns("OrderID"), _                 ds.Tables("OrderDetails").Columns("OrderID")).Nested = True                   ' XSLT to use for transforming this dataset.                 sXSL = "CustOrders.xslt"                 Fname = "CustomerOrders.Doc"           End Select           ' Close the connection to the data source.         nwindConn.Close()           'Transform the dataset by using the appropriate stylesheet.         Dim xmlDoc As New XmlDataDocument(ds)         Dim xslTran As New System.Xml.Xsl.XslCompiledTransform         Dim XSLTsettings As New XsltSettings         XSLTsettings.EnableDocumentFunction = True         XSLTsettings.EnableScript = True         xslTran.Load(SPath + sXSL, XsltSettings, Nothing)           ' Stream the results of the transformation to Word.         Dim writer As New XmlTextWriter( _          SPath + Fname, System.Text.Encoding.Default)         xslTran.Transform(xmlDoc, writer)         writer.Close()     End Sub End Class
  6.	Add the following lines of code at the top of WebForm1.aspx.vb, before the WebForm1 class implementation: Imports System.Data.SqlClient Imports System.Xml Imports System.Xml.Xsl
  7.	On the Project menu, click Add New Item. From the list of templates, click XSLT File, name the file Contacts.xslt, and then click Open.
  8.	Replace the contents of Contacts.xslt with the following: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts" > <xsl:output method="text"/> <xsl:template match="/"> <xsl:text>{\rtf1</xsl:text> <xsl:text>{\fonttbl{\f0\froman\fcharset0\fprq2 Times New Roman;}{\f1\fswiss\fcharset0\fprq2 Arial;}}</xsl:text> <xsl:text>{\header\pard\fs50 My Customer Contacts}</xsl:text> <xsl:text>{\footer\pard\fs18 Page {\field{\*\fldinst PAGE}</xsl:text> <xsl:text>{\fldrslt }} of {\field{\*\fldinst NUMPAGES}{\fldrslt 1}} \par}</xsl:text> <xsl:text>\f1\fs20</xsl:text> <xsl:for-each select="Contacts/Customers"> <xsl:text>\par\b </xsl:text><xsl:value-of select="CustomerID"/><xsl:text>\b0</xsl:text> <xsl:text>\par </xsl:text><xsl:value-of select="CompanyName"/> <xsl:text>\par </xsl:text><xsl:value-of select="ContactName"/> <xsl:text>\par </xsl:text><xsl:value-of select="Phone"/> <xsl:text>\par</xsl:text> </xsl:for-each> <xsl:text>}</xsl:text> </xsl:template> <xsl:template match="Customers"> <xsl:text>\par\b </xsl:text><xsl:value-of select="CustomerID"/><xsl:text>\b0</xsl:text> <xsl:text>\par </xsl:text><xsl:value-of select="CompanyName"/> <xsl:text>\par </xsl:text><xsl:value-of select="ContactName"/> <xsl:text>\par </xsl:text><xsl:value-of select="Phone"/> <xsl:text>\par</xsl:text> </xsl:template> </xsl:stylesheet>
  9.	On the Project menu, click Add New Item. From the list of templates, click XSLT File, name the file CustOrders.xslt, and then click Open.
  10.	Replace the contents of CustOrders.xslt with the following: <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"       xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts" >     <xsl:output method="text"/>     <msxsl:script language="VB" implements-prefix="user">     Dim CustomerTotal as Double = 0     Dim OrderSubtotal as Double = 0       Function AddToOrderSubtotal(byval amt) as Double     amt.MoveNext     OrderSubtotal = OrderSubtotal + System.Convert.ToDouble(amt.Current.Value)     return 0     End Function       Function GetOrderSubtotal     GetOrderSubtotal = OrderSubtotal     End Function       Function GetCustomerTotal     GetCustomerTotal = CustomerTotal     return 0     End Function       Function GetOrderTotal(freight)     freight.MoveNext     nFreight = System.Convert.ToDouble(freight.Current.Value)     GetOrderTotal = nFreight + OrderSubtotal     CustomerTotal = nFreight + OrderSubtotal + CustomerTotal     OrderSubtotal = 0     End Function     </msxsl:script>     <xsl:template match="CustomerOrders">       <xsl:text>{\rtf1</xsl:text>       <xsl:text>{\colortbl;\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;       \red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;       \red255\green255\blue255;\red221\green221\blue221;}</xsl:text>       <xsl:text>{\info{\title Sample RTF Document}{\author Microsoft Developer Support}}</xsl:text>       <xsl:text>{\header\pard\qc{\fs50 ASP-Generated RTF\par}{\fs18\chdate\par}\par\par}</xsl:text>       <xsl:text>{\footer\pard\qc\brdrt\brdrs\brdrw10\brsp100\fs18 Page {\field{\*\fldinst PAGE}</xsl:text>     <xsl:text>{\fldrslt }} of {\field{\*\fldinst NUMPAGES}{\fldrslt 1}} \par}</xsl:text>       <xsl:apply-templates select="Customers"/>       <xsl:text>}</xsl:text>     </xsl:template>     <xsl:template match="Customers">       <xsl:text>\par\pard\fs20\cf2\qr\b </xsl:text>     <xsl:value-of select="CustomerID"/>     <xsl:text>\cf0\b0</xsl:text>     <xsl:text>\par\pard </xsl:text>     <xsl:value-of select="CompanyName"/>     <xsl:text>\par </xsl:text>     <xsl:value-of select="Address"/>     <xsl:text>\par </xsl:text>     <xsl:value-of select="City"/>     <xsl:text>, </xsl:text>     <xsl:value-of select="Region"/>     <xsl:text> </xsl:text>     <xsl:value-of select="PostalCode"/>     <xsl:text>\par </xsl:text>     <xsl:value-of select="Country"/>     <xsl:text>\par\par</xsl:text>       <xsl:apply-templates select="Orders"/>       <xsl:text>\trowd\cellx7000\cellx9000\pard\intbl\ql\b\cbpat1 </xsl:text>     <xsl:text>Order Total for the Current Period:\cell </xsl:text>     <xsl:text>\qr</xsl:text>     <xsl:variable name="CustTtl" select="user:GetCustomerTotal()"/>     <xsl:value-of select="format-number($CustTtl,'$###0.00')"/>     <xsl:text>\cell</xsl:text>     <xsl:text>\pard\intbl\row</xsl:text>       <xsl:text>\pard\par\pard</xsl:text>     <xsl:text>\pard\plain\fs18\cf6\qc</xsl:text>     <xsl:choose>       <xsl:when test="$CustTtl = 0">         <xsl:text>\b We've missed hearing from you!\b0 </xsl:text>         <xsl:text> At your convenience, please call your personal sales representative </xsl:text>         <xsl:text>so that we may discuss our specials for new and returning customers!</xsl:text>       </xsl:when>       <xsl:when test="$CustTtl > 2000">         <xsl:text>\b Congratulations!\b0   Your purchases for this period qualify you for a \b 20%\b0 </xsl:text>         <xsl:text> discount on one of your next orders. To take advantage of this offer, provide </xsl:text>         <xsl:text>the coupon code ABC123XYZ when placing your order.</xsl:text>       </xsl:when>       <xsl:otherwise>         <xsl:text> We value your patronage with Northwind Traders and would love to hear from you. </xsl:text>         <xsl:text>If you have any questions about our upcoming line of products or if you want </xsl:text>         <xsl:text>a catalog for the coming season, call 1-888-000-000.</xsl:text>       </xsl:otherwise>     </xsl:choose>     <xsl:text>\par\pard</xsl:text>     <xsl:text>\par \page</xsl:text>     </xsl:template>     <xsl:template match="Orders">       <xsl:text>\trowd\cellx9000\pard\intbl\cbpat9</xsl:text>     <xsl:text>\ql\b </xsl:text>     <xsl:value-of select="OrderID"/>     <xsl:text>\b0\cell </xsl:text>     <xsl:text>\pard\intbl\row</xsl:text>       <xsl:apply-templates select="OrderDetails"/>       <xsl:text>\trowd\cellx7000\cellx9000\pard\intbl</xsl:text>     <xsl:text>\qr Subtotal:\cell </xsl:text>     <xsl:value-of select="format-number(user:GetOrderSubtotal(),'$###0.00')"/>     <xsl:text>\cell</xsl:text>     <xsl:text>\pard\intbl\row</xsl:text>       <xsl:text>\trowd\cellx7000\cellx9000\pard\intbl</xsl:text>     <xsl:text>\qr Freight:\cell </xsl:text>     <xsl:value-of select="format-number(Freight,'$###0.00')"/>     <xsl:text>\cell</xsl:text>     <xsl:text>\pard\intbl\row</xsl:text>       <xsl:text>\trowd\cellx7000\cellx9000\pard\intbl</xsl:text>     <xsl:text>\qr Total:\cell </xsl:text>     <xsl:value-of select="format-number(user:GetOrderTotal(Freight), '$###0.00')"/>     <xsl:text>\cell</xsl:text>     <<