TweetFollow Us on Twitter

The REALbasic-Office secret decoder ring

Volume Number: 20 (2004)
Issue Number: 7
Column Tag: Programming

REALBasic Best Practice

by Guyren G Howe

The REALbasic-Office secret decoder ring

How to control Microsoft Office from REALbasic

Last time, I discussed some ways of "breaking out" of REALbasic, to call on external applications to supplement what your REALbasic application can do. I lamented that I was unable to explore controlling Microsoft Office from RB, because although it can do that really well, the documentation for this feature was both inadequate and horribly, egregiously wrong.

Since that time, things have gotten slightly better. There is now a web page (http://www.realsoftware.com/office2004mac/) from which you can download a small number of actual, correct, working examples. But, there is still no documentation to speak of, and what there is, it would lead you to believe that using Office from REALbasic is harder than it really is.

So in this article, I will present the apparently entirely undocumented, super-secret really easy way to control Office from RB.

But First...

Since I can't find anywhere else to put it, a quick aside: there were some errors in my article on REAL World. I had said I thought there were fifty attendees, but it turns out there were over a hundred. I also mistakenly wrote that there were three seminars per day, but what I meant to say was that there were three or four sessions at a time, and a bunch of sessions each day. It was quite a good seminar program.

Ta-Da! The Big Secret

Microsoft Office, on both Macintosh and Windows, incorporates a fairly sophisticated built-in scripting language called Visual Basic for Applications (VBA). The really super secret of controlling Microsoft Office from REALbasic -- the one I had to offer up my first-born and a handful of magic beans for -- is that REALbasic understands the entire VBA namespace. This means that essentially every term you can see in Word's object inspector (more on that in a moment) can be used in REALbasic. Classes from Word can be used in REALbasic just by sticking the letters Word in front of them (and similarly for Excel and PowerPoint), and method and function calls on those classes can (with one type of exception) be used unmodified. So, for example, you can fetch the text of the front most Word document with the following code:

GetTheFrontDocument()
A function that returns the text of the front most Word document
Sub GetTheFrontDocument() As String
dim w As New WordApplication
dim doc As WordDocument = w.ActiveDocument()
Return doc.Content().Text
End Sub

So the Word Document class becomes the WordDocument class in REALbasic. Also, the global methods and properties in VBA become method calls and properties on WordApplication, ExcelApplication, or PowerPointApplication in REALbasic.

There. The secret's out. Not much to it, really. Now I'll just fill in a few details, and explore some code examples.

Named Arguments

The only major difference between VBA and REALbasic is that VBA supports a nice feature called named arguments. This means that rather than having to remember, and write an ordered list of bare values for the arguments to a method call, you can provide them as assignment statements, like the last line of this code snippet (taken from the REALbasic built-in help for the Office class):

Example of Replace in VBA

Example Visual Basic for Applications code to perform a find and replace

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "find this"
.Replacement.Text = "replace with"
.Wrap = wdFindContinue
.Format = false
.MatchCase = false
.MatchWholeWord = false
.MatchWildcards = false
.MatchSoundsLike = false
.MatchAllWordForms = false
End With
Selection.Find.Execute Replace:=wdReplaceAll

This is great, but the translation into REALbasic, given in the built-in documentation, doesn't work. Here's how to actually do it:

The previous example, in REALbasic

Using the secret decoder ring, we translate the last example into REALbasic

Dim word as New WordApplication
Dim find As WordFind = word.ActiveDocument.Content.Find

find.ClearFormatting
find.Replacement.ClearFormatting
find.text = "find this"
find.Replacement.Text = "replace with"
find.Wrap = Office.wdFindContinue
find.Format = False
find.MatchCase = false
find.MatchWholeWord = false
find.MatchWildcards = false
find.MatchSoundsLike = false
find.MatchAllWordForms = false

// Now the fun stuff

Dim replaceParam as New OLEParameter
replaceParam.Value = Office.wdReplaceAll

// according to the docs on Find.Execute the Replace parameter is the 11th
replaceParam.Position = 11

find.Execute replaceParam

If you had more than one named parameter, you would use multiple OLEParameter objects, one for each named parameter, and you just pass them all, in any order, to the method call.

A Larger Example

I'm now going to go through a small, entirely artificial piece of code, intended to cram together a variety of techniques. This article is not remotely large enough to attempt even a quick survey of Office's impressive programming features, but I hope I've presented you with just enough to get you up, and going on your own.

The code will search through all the comments in the front most Word document. It will find all the comments that start with the word test followed by two numbers (all separated by spaces). Any such comment will be replaced by a table with the number of rows and columns given by the two numbers, and then the words "Hello, World" will be placed into the cell in the upper, left corner of the table. I'll go through not only the code, but more importantly how I worked out how to write it. I'll be working with Word X, not Word 2004. I don't consider the new version a worthwhile upgrade, so what you see will probably be slightly different in the newer version (although the code should still work the same).

Note that while the example, and my comments refer to Word, the same ideas apply to Excel and PowerPoint (except that, oddly enough, PowerPoint doesn't support macro recording).

Word's Macro Environment

Most of what you need to know, you can learn from within Word itself. In the Tools menu, you'll find a Macro submenu, which provides access to Word's programming features. In this menu, the REALbasic Editor command just launches REALbasic. The Record New Macro... command opens a dialog for you to name a new Macro, which is what Office calls a program written in VBA.


Figure SEQ Figure \* ARABIC 1: The Record Macro dialog

After you click OK in this dialog, you can execute a series of actions, and Word will translate those actions into a VBA program to do the same thing again. Note that you can't click to move the insertion point while you're recording a macro. There isn't any way for Word to turn that action, that only makes sense in that document (scrolling to that point, in a window of that size) into a set of actions repeatable anywhere.

While you're recording the macro, Word shows a tiny little window with stop and pause buttons:


Figure SEQ Figure \* ARABIC 2: The Macro recording palette

Now, just carry out the sorts of actions you want REALbasic to control, then hit the stop button in the little window. Next, go back to the Macro submenu and choose Macros.... You will then see a window showing the name of the macro you just recorded, along with any other macros you have previously written or recorded:


Figure SEQ Figure \* ARABIC 3: The Macros window

Click on the name of the macro you just recorded, then click Edit. Here is what I got when I recorded a macro in which I created a table along the lines of our little project:


Figure SEQ Figure \* ARABIC 4: The Word Macro editor

There are also other windows showing at this point, but describing this whole programming environment is outside the scope of this document, so I will stick to only what you need most for your REALbasic work.

The Object Browser

Recording a macro will generally give you a good idea of the kind of commands you need to execute to do something. You can also examine every available command, and class in Word by opening the Object Browser. You can do this by clicking a button on the toolbar you see in the macro editing environment:


Figure SEQ Figure \* ARABIC 5: The Object Browser button

Alternately, you can choose Object Browser from the View menu. Any of these will show Word's Object Browser:


Figure SEQ Figure \* ARABIC 6: The Word Object Browser

Note the search box in the middle, at the top (results appear in the blank area). The business part for our purposes is below the blank area. The list of Classes is comprehensive. <globals>, shown in the screen shot, are the methods, and properties available in the WordApplication class in REALbasic.

When you click on a class, the properties, methods, and constants are shown to the right of the class name. If you click on something shown there, its details are shown below, and it is all hyperlinked. This is actually how I developed the code for the example. Well, that, and examining the one working Word example that REAL Software has published.

There are some books on VBA (I understand the O'Reilly book -- ISBN 1-56592-358-8 -- is quite good), and a lot of material online. A bit of drill-down in the Object Browser, and some experimentation, gave me code to do my little example fairly easily.

The Example

To finish up, the example.

ReplaceCommentsWithTables

An example that finds comments in the front most Word document, replacing those that begin with the word table with a table whose dimensions depend on the rest of the comment

Sub ReplaceCommentsWithTables()
  Dim word as New WordApplication
  Dim c As WordComments
  c = word.ActiveDocument.Comments
  
  Dim counter As Integer
  Dim comment As WordComment
  Dim content() As String
  Dim width, height as Integer
  Dim location As WordRange
  if c.Count > 0 then
    counter = 1
    do
      comment = c.Item(counter)
      content = Split(comment.Range.Text(), " ")
      if content(0) = "table" then
        height = content(1).val
        width = content(2).val
        location = comment.Reference
        location.Tables.Add(comment.Reference, width, height)
        location.Tables.Item(1).Cell(1, 1).Range.Text = "hello, world"
      else
        counter = counter + 1
      end if
    loop until counter >= c.Count
  end if
End Sub

The only things that bear explaining here are that a Range in VBA is any point in the document, or any continuous section of text in the document. A Comment object has several Range properties. The one called Range is the actual content of the comment. The one called Reference is the location of the comment in the main text. Also, adding something (in this case, a table) to a range replaces the range's contents (in this case, the comment) with that table. Finally, collections of things in VBA will usually have an Item() function to fetch the collection's contents.

Apart from that, I hope you find that the mostly clear names for things in VBA, the ready availability of example code online, and the ability to record a macro if you want to know how to do something, will make this very useful feature of REALbasic surprisingly easy to use.

Brief aside: Just before I finished this, REAL Software announced a product called Office Power Pack, based on this feature in REALbasic. And Microsoft themselves have written some of the software that ships with Office (the installer and the query tool) in REALbasic. This is a practical feature indeed, and the market for Office add-ons or applications that can work with Office has to be substantial.

Finally, I will note that REALbasic's Office features are almost perfectly cross-platform, without modification other than to path strings and the like.


Guyren G Howe works in artificial intelligence research, after years of work as a technical write,r and developer. He is married with one child, is an Australian, and lives in Austin, Texas. Guyren has been working with REALbasic for several years. Most notably, he wrote the REALbasic Curriculum Project, an extensive computer science curriculum, for REAL Software (available from the REALbasic website). You can contact Mr. Howe at realbasic@mactech.com

 
AAPL
$119.00
Apple Inc.
+1.40
MSFT
$47.75
Microsoft Corpora
+0.28
GOOG
$540.37
Google Inc.
-0.71

MacTech Search:
Community Search:

Software Updates via MacUpdate

Skype 7.2.0.412 - Voice-over-internet ph...
Skype allows you to talk to friends, family and co-workers across the Internet without the inconvenience of long distance telephone charges. Using peer-to-peer data transmission technology, Skype... Read more
HoudahSpot 3.9.6 - Advanced file search...
HoudahSpot is a powerful file search tool built upon MacOS X Spotlight. Spotlight unleashed Create detailed queries to locate the exact file you need Narrow down searches. Zero in on files Save... Read more
RapidWeaver 6.0.3 - Create template-base...
RapidWeaver is a next-generation Web design application to help you easily create professional-looking Web sites in minutes. No knowledge of complex code is required, RapidWeaver will take care of... Read more
iPhoto Library Manager 4.1.10 - Manage m...
iPhoto Library Manager lets you organize your photos into multiple iPhoto libraries. Separate your high school and college photos from your latest summer vacation pictures. Or keep some photo... Read more
iExplorer 3.5.1.9 - View and transfer al...
iExplorer is an iPhone browser for Mac lets you view the files on your iOS device. By using a drag and drop interface, you can quickly copy files and folders between your Mac and your iPhone or... Read more
MacUpdate Desktop 6.0.3 - Discover and i...
MacUpdate Desktop 6 brings seamless 1-click installs and version updates to your Mac. With a free MacUpdate account and MacUpdate Desktop 6, Mac users can now install almost any Mac app on macupdate.... Read more
SteerMouse 4.2.2 - Powerful third-party...
SteerMouse is an advanced driver for USB and Bluetooth mice. It also supports Apple Mighty Mouse very well. SteerMouse can assign various functions to buttons that Apple's software does not allow,... Read more
iMazing 1.1 - Complete iOS device manage...
iMazing (was DiskAid) is the ultimate iOS device manager with capabilities far beyond what iTunes offers. With iMazing and your iOS device (iPhone, iPad, or iPod), you can: Copy music to and from... Read more
PopChar X 7.0 - Floating window shows av...
PopChar X helps you get the most out of your font collection. With its crystal-clear interface, PopChar X provides a frustration-free way to access any font's special characters. Expanded... Read more
OneNote 15.4 - Free digital notebook fro...
OneNote is your very own digital notebook. With OneNote, you can capture that flash of genius, that moment of inspiration, or that list of errands that's too important to forget. Whether you're at... Read more

Latest Forum Discussions

See All

Lucha Amigos (Games)
Lucha Amigos 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Forget Ninja Turtles, and meet Wrestlers Turtles! Crazier, Spicier and…Bouncier! Sling carapaces of 7 Luchadores to knock all... | Read more »
Raby (Games)
Raby 1.0.3 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0.3 (iTunes) Description: ***WARNING - Raby runs on: iPhone 5, iPhone 5C, iPhone 5S, iPhone 6, iPhone 6 Plus, iPad Mini Retina, iPad Mini 3, iPad 4, iPad Air,... | Read more »
Oddworld: Stranger's Wrath (Games)
Oddworld: Stranger's Wrath 1.0 Device: iOS Universal Category: Games Price: $5.99, Version: 1.0 (iTunes) Description: ** PLEASE NOTE: Oddworld Stranger's Wrath requires at least an iPhone 4S, iPad 2, iPad Mini or iPod Touch 5th gen... | Read more »
Bounce On Back (Games)
Bounce On Back 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: | Read more »
Dwelp (Games)
Dwelp 1.0 Device: iOS Universal Category: Games Price: $.99, Version: 1.0 (iTunes) Description: === 50% off for a limited time, to celebrate release === Dwelp is an elegant little puzzler with a brand new game mechanic. To complete a... | Read more »
Make Way for Fat Chicken, from the Maker...
Make Way for Fat Chicken, from the Makers of Scrap Squad Posted by Jessica Fisher on November 26th, 2014 [ permalink ] Relevant Games has announced they will be releasing their reverse tower defense game, | Read more »
Tripnary Review
Tripnary Review By Jennifer Allen on November 26th, 2014 Our Rating: :: TRAVEL BUCKET LISTiPhone App - Designed for the iPhone, compatible with the iPad Want to create a travel bucket list? Tripnary is a fun way to do exactly that... | Read more »
Ossian Studios’ RPG, The Shadow Sun, is...
Ossian Studios’ RPG, The Shadow Sun, is Now Available for $4.99 Posted by Jessica Fisher on November 26th, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Mmmm, Tasty – Having the Angry Birds for...
The very first Angry Birds debuted on iOS back in 2009. When you sit back and tally up the number of Angry Birds games out there and the impact they’ve had on pop culture as a whole, you just need to ask yourself: “How would the birds taste... | Read more »
Rescue Quest Review
Rescue Quest Review By Jennifer Allen on November 26th, 2014 Our Rating: :: PATH BASED MATCH-3Universal App - Designed for iPhone and iPad Guide a wizard to safety by matching gems. Rescue Quest might not be an entirely original... | Read more »

Price Scanner via MacPrices.net

Black Friday: 15% off iTunes Gift Cards
Staples is offering 15% off $50 and $100 iTunes Gift Cards on their online store as part of their Black Friday sale. Click here for more information. Shipping is free. Best Buy is offering $100... Read more
BEVL Releases Dock Tailored for iPhone 6 and...
Seattle based BEVL has released their first product: an iPhone dock that is divergent in build quality, rock-solid function and visual simplicity to complement the iPhone. BEVL is now accepting... Read more
Black Friday: $150 off 13-inch Retina MacBook...
 Best Buy has 13-inch 2.6GHz Retina MacBook Pros on sale for $150 off MSRP on their online store as part of their Black Friday sale. Choose free shipping or free local store pickup (if available).... Read more
Black Friday: $300 off 15-inch Retina MacBook...
 B&H Photo has the new 2014 15″ Retina MacBook Pros on sale for $300 off MSRP as part of their Black Friday sale. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.2GHz Retina... Read more
Black Friday: Up to $140 off MacBook Airs, fr...
 B&H Photo has 2014 MacBook Airs on sale for up to $140 off MSRP as part of their Black Friday sale. Shipping is free, and B&H charges NY sales tax only: - 11″ 128GB MacBook Air: $799 $100... Read more
Black Friday: 13-inch 2.5GHz MacBook Pro on s...
 Best Buy has the 13″ 2.5GHz MacBook Pro on sale for $899.99 on their online store as part of their Black Friday sale. Choose free shipping or free instant local store pickup (if available). Their... Read more
Black Friday: 21-inch 1.4GHz iMac on sale for...
 Best Buy has the 21″ 1.4GHz iMac on sale for $899.99 on their online store as part of their Black Friday sale. Their price is $200 off MSRP. Choose free shipping or free local store pick up. Price... Read more
Black Friday iPad Air 2 sale prices, $100 off...
 Best Buy has iPad Air 2s on sale for $100 off MSRP on their online store for Black Friday. Choose free shipping or free local store pickup (if available). Sale prices available for online orders... Read more
2014 1.4GHz Mac mini on sale for $449, save $...
 B&H Photo has the new 1.4GHz Mac mini on sale for $449.99 including free shipping plus NY tax only. Their price is $50 off MSRP, and it’s the lowest price available for this new model. Adorama... Read more
Early Black Friday pricing on 27-inch 5K iMac...
 B&H Photo continues to offer Black Friday sale prices on the 27″ 3.5GHz 5K iMac, in stock today and on sale for $2299 including free shipping plus NY sales tax only. Their price is $200 off MSRP... Read more

Jobs Board

*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
Senior Event Manager, *Apple* Retail Market...
…This senior level position is responsible for leading and imagining the Apple Retail Team's global event strategy. Delivering an overarching brand story; in-store, Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
*Apple* Solutions Consultant (ASC) - Apple (...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.