Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 30Working 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 ...