November 20 2008 
The Word of Merge - Part 1
How you can integrate with Microsoft
In a previous newsletter , we talked about the powerful 'merge' function and how you can make your code more readable while easily combining static and dynamic data.
At the end of that article, we touched on the topic of using the 'merge' function with raw RTF text files to create Word documents. In this follow-up article, we will go into more detail and show how you can use this technique to integrate with Microsoft Office, using Word as a reporting mechanism.
Let's start with a simple example. Open up Microsoft Word, type in a single line:
[[theMessage]]
And save it to disk as an RTF document named 'theMessage.rtf'
If you're wondering about the double brackets, I use those for two reasons: the 'merge' function readily substitutes anything inside double brackets with the corresponding value of the expression. And it doesn't seem to interfere with the RTF file format, which is another major bonus.
Next we go to Revolution and make a new stack with a button 'Create a Message'. Set its script to:
on mouseUp local theMessage, theTemplateFile, theMergedFile, theRTFtext -- ask "What message do you have for the world?" with \ "Hello, world!" if it is empty then exit mouseUp put it into theMessage answer file "Where is 'theMessage.rtf' template file?" if it is empty then exit mouseUp put it into theTemplateFile ask file "Save your message as:" if it is empty then exit mouseUp put it into theMergedFile -- put URL("binfile:" & theTemplateFile) into theRTFtext set the fileType to "MSWDRTF " put merge(theRTFtext) into URL ("binfile:" & theMergedFile) end mouseUp
Save the script, and click on the button. Enter some inspired message, locate the RTF file that we saved earlier, and choose a nice spot for the resulting message file. Then double-click on the output file to find that your message was nicely integrated with the RTF document.
This was admittedly a very simplified example, but it shows the basic technique. But before we move on to a real-world example, here's a very important tip - it cost me quite some time (and hair) until I figured it out:
When you modify an existing RTF template file, always use 'Save As' to completely replace the existing file. Otherwise, some versions of Word may leave you with a mangled RTF file where some of your tags could be split in half, and thus fail to be merged.
Now that we've made a mental note of that, let's move on to something a little more complex, and add formatting, page header and footer, and a table to construct a fairly good shipping bill. The beauty of RTF is that Microsoft updates the specficiations every time a new version of Microsoft Word comes out - so you can use virtually any feature of Microsoft Word in your template files.
As you can see, we've put in a bunch of placeholder tags with square brackets. Again, we will replace these with the content of our variables using the 'merge' function. So let's save the shipping bill as another RTF file with the inspired name 'theShippingBill.rtf' and return to Revolution to create another button 'Create Shipping Bill' - note that the following script contains no database access or actual calculations.
on mouseUp local theTemplateFile, theMergedFile, theRTFtext -- answer file "Where is 'theShippingBill.rtf' template file?" if it is empty then exit mouseUp put it into theTemplateFile ask file "Save your shipping bill as:" if it is empty then exit mouseUp put it into theMergedFile -- local bill_ship_date, bill_internal_id, bill_external_id put "11/15/2008" into bill_ship_date put "SB 12345" into bill_internal_id put "-" into bill_external_id local bill_name, bill_address, bill_zipcode, \ bill_municipality, bill_country put "Homerio Simpsonian" into bill_name put "732, Everblue Hair" into bill_address put "85558" into bill_zipcode put "Sprinklefield" into bill_municipality put "XX USA" into bill_country -- local art_1, description_1, qty_1, price_1, total_1 FetchArticleData art_1, description_1, qty_1, price_1, total_1 local art_2, description_2, qty_2, price_2, total_2 FetchArticleData art_2, description_2, qty_2, price_2, total_2 local art_3, description_3, qty_3, price_3, total_3 FetchArticleData art_3, description_3, qty_3, price_3, total_3 local art_4, description_4, qty_4, price_4, total_4 FetchArticleData art_4, description_4, qty_4, price_4, total_4 local art_5, description_5, qty_5, price_5, total_5 FetchArticleData art_5, description_5, qty_5, price_5, total_5 local art_6, description_6, qty_6, price_6, total_6 FetchArticleData art_6, description_6, qty_6, price_6, total_6 local art_7, description_7, qty_7, price_7, total_7 FetchArticleData art_7, description_7, qty_7, price_7, total_7 local art_8, description_8, qty_8, price_8, total_8 FetchArticleData art_8, description_8, qty_8, price_8, total_8 local art_9, description_9, qty_9, price_9, total_9 FetchArticleData art_9, description_9, qty_9, price_9, total_9 -- local subtotal_art, subtotal_sah, subtotal_tax, bill_total, \ bill_due_date put "53.73" into subtotal_art put "4.99" into subtotal_sah put "8.81" into subtotal_tax put "67.53" into bill_total put "12/31/2008" into bill_due_date -- local the_message put "We're closed for Thanksgiving, so ship fast!" into \ the_message -- put URL("binfile:" & theTemplateFile) into theRTFtext set the fileType to "MSWDRTF " put merge(theRTFtext) into URL ("binfile:" & theMergedFile) end mouseUp on FetchArticleData @pArticle, @pDescription, @pQuantity, \ @pPrice, @pTotal put "ABC123" into pArticle put "Some article from our warehouse" into pDescription put "3.00" into pQuantity put "1.99" into pPrice put "5.97" into pTotal end FetchArticleData
Click the button, and you end up with a lovely shipping bill.
In this example, we used a fixed template with room for 9 article lines. Once you learn the intricacies of the RTF file format, you can take apart your template files and slice them into chunks that you can merge with the relevant data, adding more lines to your table as needed, as well as complete paragraphs. And don't forget that ordinary Revolution fields also have an 'rtfText' property, so you can take the styling information from your fields and transfer that into your RTF documents.
In the next installment, we'll discuss how you can leverage VBScript on Windows and AppleScript on MacOS X, in order to automatically open and print your freshly created RTF files. In the meantime, here are a few links to more resources for the RTF file format:
Have fun creating RTF-documents and merging them from Revolution!
Jan Schenkel is the developer behind Quartam Reports and Quartam PDF Library for Revolution. Find out more at www.quartam.com
