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

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Firetask Pro 4.2.2 - Innovative task man...
Firetask Pro uniquely combines the advantages of classical priority-and-due-date-based task management with GTD. Stay focused and on top of your commitments - Firetask Pro's "Today" view shows all... Read more
Bookends 13.4.3 - Reference management a...
Bookends is a full-featured bibliography/reference and information-management system for students and professionals. Bookends uses the cloud to sync reference libraries on all the Macs you use.... Read more
LibreOffice 6.4.5.2 - Free, open-source...
LibreOffice is an office suite (word processor, spreadsheet, presentations, drawing tool) compatible with other major office suites. The Document Foundation is coordinating development and... Read more
Thunderbird 68.10.0 - Email client from...
As of July 2012, Thunderbird has transitioned to a new governance model, with new features being developed by the broader free software and open source community, and security fixes and improvements... Read more
Firefox 78.0.1 - Fast, safe Web browser.
Firefox offers a fast, safe Web browsing experience. Browse quickly, securely, and effortlessly. With its industry-leading features, Firefox is the choice of Web development professionals and casual... Read more
BetterTouchTool 3.389 - Customize multi-...
BetterTouchTool adds many new, fully customizable gestures to the Magic Mouse, Multi-Touch MacBook trackpad, and Magic Trackpad. These gestures are customizable: Magic Mouse: Pinch in / out (zoom)... Read more
Slack 4.7.0 - Collaborative communicatio...
Slack brings team communication and collaboration into one place so you can get more work done, whether you belong to a large enterprise or a small business. Check off your to-do list and move your... Read more
OsiriX Lite 11.0.3 - 3D medical image pr...
OsiriX Lite is an image processing software dedicated to DICOM images (".dcm" / ".DCM" extension) produced by medical equipment (MRI, CT, PET, PET-CT, ...) and confocal microscopy (LSM and BioRAD-PIC... Read more
Wireshark 3.2.5 - Network protocol analy...
Wireshark is one of the world's foremost network protocol analyzers, and is the standard in many parts of the industry. It is the continuation of a project that started in 1998. Hundreds of... Read more
Dabble 1.6.1 - Organize your manuscript,...
Dabble organizes your manuscript, story notes, and plot. Dabble simplifies the story, leaving more room in your brain to create, which is what being a writer is really about. Organize your story.... Read more

Latest Forum Discussions

See All

Pokemon Go's July Community Day wil...
Pokemon Go developers have announced the details concerning the upcoming Gastly Community Day. This particular event was selected by the players of the game after the Gas Pokemon came in second place after a poll that decided which Pokemon would... | Read more »
Clash Royale: The Road to Legendary Aren...
Supercell recently celebrated its 10th anniversary and their best title, Clash Royale, is as good as it's ever been. Even for lapsed players, returning to the game is as easy as can be. If you want to join us in picking the game back up, we've put... | Read more »
Detective Di is a point-and-click murder...
Detective Di is a point-and-click murder mystery set in Tang Dynasty-era China. You'll take on the role of China's best-known investigator, Di Renjie, as he solves a series of grisly murders that will ultimately lead him on a collision course with... | Read more »
Dissidia Final Fantasy Opera Omnia is se...
Dissidia Final Fantasy Opera Omnia, one of Square Enix's many popular mobile RPGs, has announced a plethora of in-game events that are set to take place over the summer. This will include several rewards, Free Multi Draws and more. [Read more] | Read more »
Sphaze is a neat-looking puzzler where y...
Sphaze is a neat-looking puzzler where you'll work to guide robots through increasingly elaborate mazes. It's set in a visually distinct world that's equal parts fantasy and sci-fi, and it's finally launched today for iOS and Android devices. [... | Read more »
Apple Arcade is in trouble
Yesterday, Bloomberg reported that Apple is disappointed in the performance of Apple Arcade and will be shifting their approach to the service by focusing on games that can retain subscribers and canceling other upcoming releases that don't fit... | Read more »
Pixel Petz, an inventive platform for de...
Pixel Petz has built up a sizeable player base thanks to its layered, easy-to-understand creative tools and friendly social experience. It revolves around designing, trading, and playing with a unique collection of pixel art pets, and it's out now... | Read more »
The King of Fighters Allstar's late...
The King of Fighters ALLSTAR, Netmarble's popular action RPG, has once again been updated with a plethora of new content. This includes battle cards, events and 21 new fighters, which increases the already sizeable roster even more. [Read more] | Read more »
Romancing SaGa Re;univerSe, the mobile s...
Square Enix latest mobile spin-off Romancing SaGa Re;univerSe is available now globally for both iOS and Android. It initially launched in Japan back in 2018 where it's proven to be incredibly popular, so now folks in the West can finally see what... | Read more »
Away: Journey to the Unexpected is a sto...
Away: Journey to the Unexpected looks really quite lovely. Stylish, cute, and clearly heavily inspired by Japanese animation, it's amongst the best-looking mobile games on the horizon. Developed by a two-person team, this story-driven rogue-lite... | Read more »

Price Scanner via MacPrices.net

July 4th Sale: Woot offers wide range of Macs...
Amazon-owned Woot is blowing out a wide range of Apple Macs and iPads for July 4th staring at $279 and ranging up to just over $1000. Models vary from older iPads and 11″ MacBook Airs to some newer... Read more
Apple Pro Display XDR with Nano-Texture Glass...
Abt Electronics has Apple’s new 32″ Pro Display XDR model with the nano-texture glass in stock and on sale today for up to $144 off MSRP. Shipping is free: – Pro Display XDR (nano-texture glass): $... Read more
New 2020 Mac mini on sale for up to $100 off...
Amazon has Apple’s new 2020 Mac minis on sale today for $40-$100 off MSRP with prices starting at $759. Shipping is free: – 2020 4-Core Mac mini: $759 $40 off MSRP – 2020 6-Core Mac mini: $998.99 $... Read more
July 4th Sale: $100 off every 2020 13″ MacBoo...
Apple resellers have new 2020 13″ MacBook Airs on sale for $100 off Apple’s MSRP as part of their July 4th sales. Starting at $899, these are the cheapest new 2020 MacBooks for sale anywhere: (1) B... Read more
This hidden deal on Apple’s site can save you...
Are you a local, state, or federal government employee? If so, Apple offers special government pricing on their products, including AirPods, for you as well as immediate family members. Here’s how... Read more
Apple Watch Series 3 models on sale for new l...
Amazon has Apple Watch Series 3 GPS models on sale for $30 off MSRP, starting at only $169. Their prices are the lowest available for these models from any Apple reseller. Choose Amazon as the seller... Read more
Deal Alert! Get these refurbished 2018 13″ Ma...
Apple has restocked and lowered prices on select Certified Refurbished 2018 13″ MacBook Airs, starting at only $679. Each MacBook features a new outer case, comes with a standard Apple one-year... Read more
July 4th Sale: 13″ 2.0GHz MacBook Pros for $2...
B&H Photo has new 2020 13″ 2.0GHz MacBook Pros on sale for $200 off Apple’s MSRP as part of their July 4th sale. Prices start at $1599. These are the same MacBook Pros sold by Apple in their... Read more
July 1 only: $100 off Apple iPhone 11, 11 Pro...
Boost Mobile is offering Apple iPhone 11, 11 Pro, and iPhone 11 Pro Max models for $100 off MSRP with service. Their discount reduces the cost of an iPhone 11/64GB to $599, iPhone 11 Pro to $899 for... Read more
Apple offers $50-$100 Education discount on i...
Purchase a new 12.9″ or 11″ iPad Pro at Apple using your Education discount, and Apple will take $50-$100 off their MSRP. All teachers, students, and staff of any educational institution with a .edu... Read more

Jobs Board

Operating Room Assistant, *Apple* Hill Surg...
Operating Room Assistant, Apple Hill Surgical Center - Full Time, Day Shift, Monday - Saturday availability required Tracking Code 62363 Job Description Operating Read more
Perioperative RN - ( *Apple* Hill Surgical C...
Perioperative RN - ( Apple Hill Surgical Center) Tracking Code 60593 Job Description Monday - Friday - Full Time Days Possible Saturdays General Summary: Under the Read more
Product Manager, *Apple* Commercial Sales -...
Product Manager, Apple Commercial Sales Austin, TX, US Requisition Number:77652 As an Apple Product Manager for the Commercial Sales team at Insight, you Read more
*Apple* Mac Product Engineer - Barclays (Uni...
Apple Mac EngineerWhippany, NJ Support the development and delivery of solutions, products, and capabilities into the Barclays environment working across technical Read more
Blue *Apple* Cafe Student Worker - Pennsylv...
…enhance your work experience. Student positions are available at the Blue Apple Cafe. Employee meal discount during working hours. Duties include food preparation, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.