Thursday, 27 May 2010

The bad old days of VBA, and opening Word from Excel

You tend to forget how good Visual Studio has become as a development environment, until you jump back into something like writing VBA macros in Excel.

This is pretty much a VB6 environment, but maybe it's just my memory but I didn't think VB6 was as bad as VBA is. Even more amazing is that if you open up Excel 2010 and click on the Visual Basic icon in the Developer tab, nothing has changed. There's good old VBA in all its glory.

Sure, you can use .NET with Excel, but only to write add-ins. These are installed separately, so you can't just send a spreadsheet off to a colleague and expect it to work if they don't also have the add-in installed. Another option would be to write a full-blown .NET application that used COM Interop to call out to Word and Excel.

So what lead me to be messing around in VBA? The scenario involves aggregating data from a large number of spreadsheets into a single Word document. If you're thinking this sounds like something better handled with SharePoint or an intranet web application, you'd be right. Probably the ".NET application with COM Interop" would also have been a better choice,  but for now resource constraints meant that the "improvised" solution was the best that could be done, and as a proof of concept it appears to be doing the job.

Just to be clear, this isn't for a LobsterPot client, rather I'm just helping out a friend. The particular problem I was asked to advise on was that the Excel VBA code was launching Word, but Word would be opened in the background. The request was to make the new Word document appear in front of Excel so that the end-user could more easily see it. Sure, they could Alt-Tab or click on the Windows task bar to bring it into focus, but anything you can do to make things easier and more obvious for users is usually a good thing.

The original code opened Word like this:

Dim wordApplication

Set wordApplication = CreateObject("Word.Application")

This works fine, but Excel (where the VBA code is running from) stays in the foreground. Adding one extra line to the code solves the problem:

Dim wordApplication

Set wordApplication = CreateObject("Word.Application")

Application.ActivateMicrosoftApp xlMicrosoftWord

The call to ActivateMicrosoftApp does the trick. One trap to be aware of is calling that method before CreateObject. That does work, but it results in 2 instances of Word opening, which is usually not desirable.


prodactor said...

How many files is it?
there is always the Open XML SDK:

David Gardiner said...

True, however I think the best solution would be not to use Excel to collect the data in the first place, but use a proper database instead (with either a regular web or SharePoint front-end)

Jon Peltier said...

You'll get two Word instances wherever you out ActivateMicrosoftApp. The one which is activated would be different.

In many cases, you would invoke GetObject instead of CreateObject, and use the existing Word instance.

David Gardiner said...

Thanks Jon,

in this case one requirements was to bring Word into focus.

Using ActivateMicrosoftApp in the order I described didn't result in two instances of Word running in my experience.

Using GetObject is a good suggestion though.