Issue 62 * December 22 2008

The Word of Merge - Part 2
How you can integrate with Microsoft

by Jan Schenkel

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.

This time we will expand that technique to leverage Microsoft Word as a reporting mechanism. Again, the 'merge' function comes in handy for building the necessary VBScript and AppleScript instances.

Going back to our simple example, we open Microsoft Word, type in a single line:

[[theMessage]]

And save it to disk as an RTF document named 'theMessage.rtf'

 

As you know by now, the 'merge' function readily substitutes anything inside double brackets with the corresponding value of the expression. So let's go to Revolution and make a new stack with a button 'Print 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)
   --
   if the platform is "MacOS" then
      put revMacFromUnixPath(theMergedFile) into theRtfFile
      put the uAppleScript of me into theAppleScript
      do merge(theAppleScript) as "AppleScript"
   else if the platform is "Windows" then
      put theMergedFile into theRtfFile
      replace "/" with "\" in theRtfFile
      put the uVBScript of me into theVBScript
      do merge(theVBScript) as "VBScript"
   else
      -- no way to automate, tell user where file was saved
      answer "The message was saved in:" & return & tab & \
      theMergedFile
   end if
end mouseUp

Save the script, select the button with the pointer tool and then open the Inspector palette. Switch to the 'Custom properties' panel and create a new custom property 'uVBScript'. Set the content of this custom property to:

Set objWord = CreateObject("Word.Application") 

Set objDoc = objWord.Documents.Open("[[theRtfFile]]") 

objDoc.PrintOut() 

objWord.Quit  

Next we create a new custom property 'uAppleScript'. Set the content of this custom property to:

tell application "Microsoft Word" 

    open "[[theRtfFile]]" 

    print "[[theRtfFile]]" 

    quit 

end tell  

Now when you click the button, whether you're on Windows or Mac, the script merges your RTF template with your data, opens the resulting file with Microsoft Word, prints it and quits the Word helper application again. To do this, we've used the 'merge' function twice: once to generate our RTF document from the template, and again to point our VBScript or AppleScript to the correct file on disk.

For more information regarding Microsoft Word automation, look at:

•  the Microsoft Office for Windows OLE documentation

•  the Microsoft Office for Mac AppleScript documentation

This concludes our series on the power of the 'merge' function. So far, we've concentrated on RTF documents, but you can use it to quickly generate all types of documents, dynamically filling templates with your data. And it also comes in handy when you want to automate task using VBScript and AppleScript.

Jan Schenkel is the developer behind Quartam Reports and Quartam PDF Library for Revolution. Find out more at www.quartam.com

 

Main Menu What's New

Get The Megabundle