How to develop MS Office cross application programs

[ispy99]

Hello, this is my first post so I apologize if I don't follow etiquette.

I have used VBA for several years, mainly in Excel, to automate some of the
work I do. I now need to do some automation work that will cross office
applications. I am reading files into Excel, reformatting them, and then
loading the data into an Access DB. I may need to do some tasks in Outlook
and Word as well. For the past couple of weeks I have been scratching around
trying to find the best way to approach the project. I played around with
the Visual Basic .NET IDE, but I did some reading on it and supposedly it
doesn't work with any applications besides Word and Excel. I tried using
VBA, but I can only seem to find an IDE that is hosted within an application.
So here are my questions:

Can the .NET IDE work with all Office apps? Is there an
application-independent VBA IDE? And, if those two answers are no, can I
control other applications from within, say, Excel? (In other words run the
code for Word, Access, or whatever from within the Excel IDE. Seems like a
clunky approach to me.) Any other thoughts or suggestions would be greatly
appreciated.

 

[Stephen Bullen]

Hi Ispy99,

(In other words run the
code for Word, Access, or whatever from within the Excel IDE. Seems like a
clunky approach to me.)

Yes you can. In the Excel IDE, click on Tools > References and select the
object model(s) you want to automate. You can then create new instances of the
application using:

Dim oWord As Word.Application
Set oWord = New Word.Application

then control word just as if you were 'inside' Word's editor. The only thing
different is that you don't automatically get the 'global' objects, so instead
of using:

Set oDoc = Documents.Open("MyFile.doc")

you need to specify the word application instance you're using:

Set oDoc = oWord.Documents.Open("MyFile.doc")

There's much more about this in our book, Professional Excel Development (see
URL below). You might want to read the relevant chapter in your local
bookstore.

Also FWIW, you probably don't need to automate Access itself to write data to
its databases - you can do that using ADO and connecting to the database
directly.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

 

[ispy99]

Stephen,

Thanks for the information, that's probably the direction I will take. So,
I guess there is not a stand alone Office VBA IDE, one separate from an
application? If not, I'd like to suggest one. It seems to me to be a
logical extension of VBA.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?aXNweTk5?=,

Thanks for the information, that's probably the direction I will take. So,
I guess there is not a stand alone Office VBA IDE, one separate from an
application? If not, I'd like to suggest one. It seems to me to be a
logical extension of VBA.

No, there is not. Nor, given the current emphasis on .NET, is there likely to
be. Closest you could get would be the classic VB.

VBA = Visual Basic for Applications. In this case, put emphasis on
Applications. It's VB customized to work with a particular application,
running within that application instance and interface. If you had a "common
Office IDE", you'd have to program everything the same as for classic VB, so
why not simply use that. Or it would be very similar to what you'd program in
any one of the application IDEs. IOW, no need, really, for an "Office IDE".

I played around with
the Visual Basic .NET IDE, but I did some reading on it and supposedly it
doesn't work with any applications besides Word and Excel.

This is incorrect. Apparently, you only looked at VSTO? You can automate
Office from VB.NET, pretty much the same was as from classic VB, as far as
syntax goes. All things being equal (i.e. no pressing need to use .NET)
classic VB would be preferable as then you're communicating totally within
COM. Calls across the .NET / COM interface can be slow, and there are other
issues, as well.

(In other words run the
code for Word, Access, or whatever from within the Excel IDE. Seems like a
clunky approach to me.)

Not really, if you postulate that the user is likely to begin the process in
one of the applications. Otherwise, if an EXE would be more appropriate, then
a VB app would be the way to go. But the code and how you go about programming
it would be basically the same.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail