Excel VBA 24-Hour Trainer (2015)

Part V
Understanding the BASICs

Lesson 30
Working with Word from Excel

With the ubiquitous presence of Microsoft Office, a common task is to create and maintain documents in Microsoft Word that either accompany, or include as part of their narrative content, data and information from Excel workbooks. From your own experience, you have probably seen situations that call for information from Word documents to be appended, printed, or exported from Word into your Excel workbook.

Word and Excel work very well together in sharing data across their respective applications. You can automate these tasks with VBA macros right from Excel, to provide your workbook projects with robust and user-friendly methods of integrating data with Word.

Activating a Word Document

In Lesson 29, you saw a macro named LateBindingTest that opened a Word document named myWordDoc.docx. However, in this complicated world of ours, a seemingly simple task like activating a Word document involves a few considerations:

· Word might not be open.

· Word is open but the document itself is not open.

· The Word document is already open.

· The Word document you want to open does not exist.

For such tasks that have multiple considerations, the “divide and conquer” approach is a good way to cover your bases. If you take each consideration in turn, you can craft a single macro to handle the entire process seamlessly.

Activating the Word Application

The basic premise of activating Word is that you must tell Excel you are leaving Excel altogether for a totally different application destination. The GetObject function is a reliable way to do this, as shown in the following macro:

Sub ActivateWord()

Dim wdApp As Object

Set wdApp = GetObject(, "Word.Application")

wdApp.Activate

End Sub

The GetObject function has two arguments, the first of which is an optional pathname argument that tells VBA where to look for a specified object. Because the pathname is not specified (which it need not be because it is optional), GetObject activates Word, because Word.Application is the object being specified in the second argument.

But what if Word is not open? If you try running the ActivateWord macro without Word being open, a runtime error occurs because VBA is being told to activate an object that can't be activated. You need to insert an error bypass in your macro to tell VBA to activate Word only if Word is open, and to open and then activate Word only if Word is closed.

You can accomplish this with the On Error Resume Next statement that monitors runtime error number 429, which is the VBA error number that occurs with the GetObject function if Word is not open. In that case, VBA opens a new instance of Word, as shown in the following modified ActivateWord macro: