Non-Excel Object Models

I’m a big fan of using the proper application for the task, e.g. Word for documents, Outlook for contact management, Access for databases. Because of that, I really like automating other applications. Most of my automation is from Excel, though there’s a bit from Access too.

When you automate another application, you expose its object model so you can access its objects and their properties and methods. That means you have to learn the syntax if you’re going to automate it successfully. Sometimes the syntax is easy and intuitive. Other times, not so much.

Because I was raised on Excel’s object model, I think it’s the best. Even when I try to be objective about it, I still think it’s better than some others that I’ve used. Below are some of my complaints about object models of other applications.

Word

Selection – I know Excel has a Selection object, and don’t get me wrong, it’s useful. But it seems that everything you want to do in Word requires the use of the Selection object. When I automate Word, I try to avoid using Selection, but sometimes it’s just impossible. Even when it’s possible I end up with the some convoluted Range object.

When I think about Word’s objects, I think about a Documents collection, a Sections collection, a Paragraphs collection, a Words collection, and a Characters collection. It seems you could do just about whatever you want with those, particularly if each object has its Parent object set up correctly.

Documents(1).Paragraphs(2).Sentence(3).Text = “Your text here”

That seems intuitive to me.

Access

DoCmd – I complain about this one every chance I get. I like to tell Access guys that their object model only has two objects; the Recordset object and the DoCmd object. On its face, DoCmd seems to be a stroke of genius. Almost anything that you can do from the UI you can do using DoCmd. But the object model isn’t supposed to simply mimic the menu structure. This line:

DoCmd.OpenForm stFrmName

should be

CurrentDb.Forms.Open(stFrmName)

at least if it was up to me.

Project

FileOpen, FileSave – I alluded to this monstrosity in a previous post. There is a Projects collection and it even has an Add method to create new Projects. Why, then, didn’t that make an Open method to open existing ones? Why not throw in a Save method? Instead I have to use Application.FileOpen.

Outlook

GetNameSpace – according to help, the only valid argument to this method is “MAPI”. Here’s an idea: don’t require an argument. Maybe it’s for “future expansion”, but it’s been the same since at least OL98. Also, in 2003, Outlook went through it’s most extensive overhaul ever. If they wanted to expand, that was probably the time.

I love complaining. If you do too, leave a comment with your object model complaint.

Posted in Uncategorized

5 thoughts on “Non-Excel Object Models

  1. My biggest complaint about Outlook automation is that due to virii and security concerns, they don’t expose enough of it. I know that if they exposed too much it would be easy for someone to write some code to take over your instance of outlook but it can be very limiting. I wanted to write some code that basically said:

    ‘If a new message has “Excel List” in the To: address, make sure that the send through account is excel@excel.com‘ and if it isn’t, change it to be “from” the account ‘excel@excel.com’

    I have several email accounts and I’ve created one just for mailing lists that I subscribe to. If I send an e-mail, I don’t want it sent from my default account and if I forget, the message will get bounced because my default account isn’t the one subscribed.

    In reading on and on about Outlook automation, you just can’t access those properties/methods. There is no way to programatically change the sending account (again, probably for security concerns – but it’s still a pain). As such, I’m now using a different mail client that lets me do what I need to!

  2. The foreign object models I’ve used most are PowerPoint and Word. They both (to me) are less intuitive than Excel’s, though as you point out, having used Excel’s so extensively lays a bit of intuition on you.

    PowerPoint is strange because it has no ActiveSlide object. You have to kludge around with ActiveWindow and so forth. There is no book you can buy to learn about PowerPoint programming, but a few web sites have useful examples. Start with (PPT MVP) Steve Rindsberg’s pptfaq.com, which has several links. Shyam Pillai has many code samples on his web site as well (http://www.mvps.org/skp/).

    Word is very strange, probably because it flows in non discrete units (no sheets, no slides, just stories and sections and pages, oh my), and you have to be flexible to deal with it. I have learned to work with the Range object in Word, rather than the Selection object; it’s not quite as bad as you make it out to be, but it isn’t easy. In fact, MSDN has several semi-helpful pages, Working with Range Objects, Working with the Selection Object, Working with Tables, Working with Shapes (search at

    msdn.microsoft.com). Word has a drawing infrastructure which I hate, both in code and in the UI. I do as little as possible in it. Some operations can only be done on inline shapes, and others on floating shapes, and it’s a real nightmare to go back and forth. What I do is get it right in the other app (usually Excel) and paste it in finally as an inline shape.

    The interoperability of these programs is very powerful. I’ve become fairly adept at automating Excel to generate information for reports (tables and charts), then building reports in Word or PowerPoint. Each project I do in either of these applications makes use of what I’ve built already (I have a standard import/export module that I’ve developed), and expands my working knowledge of their object models.

  3. i am a new user to excel VBA and i am struggling with the whole object model. Can you suggest some good text to start with.
    Thanks.

  4. I am a newcomer to the idea of VBA and Automation but my package will not allow me to access to any of the outlook objects despite installing Outlook 11.0 Object library. It wouldnt even let me do late binding. Any suggestions please? Many thanks


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.