In order for VBA to manipulate a program — or a document within a program — VBA first needs to have access to that program's object library. You might envision VBA as sort of a steering wheel that can control any program to which it has access (through an object library), as in Figure 14-1.

Some of my colleagues have Excel 2013 (it has Microsoft Outlook 15.0 Object Library). I have Excel 2016, it has Microsoft Outlook 16.0 Object Library. Using some Outlook in VBA to send out a message at end. When they want to open the Excel file, they get 'compile error', due to having the version 16.0 MISSING in VBA/Tools/References. For me, the object library was only missing from the references window while using vba editor in Inventor. When I open up excel and pull up its vba window, the object library was not missing from the list. So I clicked on the Microsoft Office 16.0 Object Library from the excel vba references window and it. Excel export to Outlook:- Missing Microsoft Outlook 16.0 object library Using assistance from online resources I have VBA to create an Outlook contact from an Excel spreadsheet. I was told the machines it was to work on were Office 2016, the same as I use.

Figure 14-1:

VBA can control any program through that program's object library.

Microsoft Outlook

Microsoft Excel

Microsoft Outlook

Microsoft PowerPoint

Microsoft Access

VBA steers the action

To write code for an Office application program, you first need to set a reference to that program's object library. To do so, starting from Microsoft Access, follow these steps:

1. In Access, open the database that contains objects to share with other programs.

2. Choose ToolsOReference from the VBA editor menu bar.

3. From the list of available references, choose the libraries for the programs you want to program.

For example, in Figure 14-2, I add references to Excel (Microsoft Excel 11.0 Object Library) and Word (Microsoft Word 11.0 Object Library).

Office XP object libraries are version 10.0, and Office 2003 libraries are 11.0. Don't worry about that, though. They work the same as far as this book is concerned.

Figure 14-2:

Choose object libraries in the References dialog box.

References Fulfill 2002

Available References:

@ Visual Basic For Applications @ Microsoft Access 10,0 Object Library 0 OLE Automation

0 Microsoft ActiveX Data Objects 2.7 Library ® Microsoft Office 10,0 Object Library 0 Microsoft DAO 3.6 Object Library V] Microsoft Excel 11,0 Object Library ✓ f licr oíofl- Woi d 11 0 Object Libr ar

□ acwzmain

□ Microsoft ActiveX Data Objects 2.1 Library

□ IAS Helper COM Component 1.0 Type Librar

□ IAS RADIU5 Protocol 1,0 Type Library LJ AcroIEHelper 1.0 Type Library l~l Arlivfi Dlrflrtnry Tvnft<;

Help a a

-Microsoft Word 11,0 Object Library

Location: C:Program FilesMicrosoft OfficeOFFICEl 1 M5WORD.OLB Language: Standard

Exploring a program's object model

After you set a reference to a program's object model, you can explore its exposed objects, properties, and methods through the Object Browser. In the VBA editor, just press F2 or choose ViewOObject Browser. To limit the display to a given program's objects, choose that program's name from the Project/ Library drop-down list. For example, in Figure 14-3, I select Excel from the Project/Library drop-down list. The classes and members in the columns beneath this list refer to Microsoft Excel and any data that might be in the currently open Excel worksheet.

In the Object Browser, classes mean objects, collections, and such, whereas members mean properties, methods, and events of (whatever is currently highlighted in the Collections pane).

For more goods on the Object Browser, see Chapter 1.

Each Office application exposes a lot of objects to VBA. Even if you limit the Object Browser to show just one program's model, you still end up with a zillion names of things. There isn't enough room in this book to define all those things. You just have to learn how to get the information you need (whatever that might be) when you need it. In the Object Browser, that generally involves clicking the name you need help with and then clicking the ? (Help) button.

Meet the Application object

Different application programs expose different object models to VBA, but all programs have in common an Application object (with a capital A). The program's Application object exposes all that program's collections and objects to VBA.

If a document is open in the program, the document's objects are also exposed to VBA. For example, when VBA opens an Excel worksheet, Excel exposes its own capabilities to VBA through its Application object. Every cell in the worksheet is also exposed. Basically, VBA can do anything in the worksheet that a person actually sitting at the worksheet could do from Excel's menus.

Excel

Figure 14-3:

Viewing classes and members of Excel's object library.

Excel

Figure 14-3:

Viewing classes and members of Excel's object library.

Excel's Application object

Members of Excel's Application object

What if I don't have Word/Excel/Outlook?

Automation between Microsoft Office programs works only with the programs currently installed on your computer. If you don't have a given program (like Microsoft Outlook) installed, you can't load its object library or control it through VBA.

Things can get confusing when you copy a database (an MDB file) that contains VBA code to a different computer. Any code that refers to Word, Excel, Outlook, or PowerPoint will fail if the current computer doesn't have those programs installed. In other words, VBA can't create those programs if they're missing. VBA can use those programs only if they already exist on the current computer.

Connecting to other programs

After you set a reference to an external program's object library, you can create instances that you program in VBA. An instance is basically the same idea as an open program window. For example, when you start Microsoft Internet Explorer on your computer, you're actually creating an instance of Internet Explorer. If you right-click a link and choose Open in New Window, a new, separate Internet Explorer window opens to show the new page. Now you have two instances of Internet Explorer open, each showing a different Web page.

Before you can create an instance of a program, you have to declare an object variable that will become the name used by VBA to refer to the program. The object variable name can be any name you like. Just try to think of a short, simple name that's meaningful. The syntax for declaring an object variable that refers to an external open program is

Dim objectVariable As New program.Application

In the syntax, objectVariable is the object variable name, and program is a reference to one of the Office application programs: Word, Excel, Outlook, or PowerPoint. The .Application part refers to the program's Application object of that program. The New keyword is optional but recommended because it ensures that the object will create a new instance of the program. Examples of declaring object variables for each of the Office programs follow.

JUNG/

JUNG/

Dim XL As New Excel.Application

Dim Wrd As New Word.Application

Dim Olk As New Outlook.Application

Dim Ppt As New PowerPoint.Application

Microsoft Outlook 16 Object Library Download

You must set a reference to a program before writing a Dim statement to declare an instance of the program.

After you declare an object variable to refer to an open instance of a program, you can then open that program (and any document) so that your VBA code has access to all the program's objects. The syntax for opening a program is

Microsoft Outlook 16 Object Library Missing

Set objectVariable As CreateObject('program.Application')

where objectVariable is the same as the name you specified in the Dim statement, and program is the name of the application program: Excel, Word, PowerPoint, or Outlook. Referring to the earlier Dim statements, the Set statements that you use for each defined object variable are the following:

I use short names for my object variables here: XL for Excel, Wrd for Word, Olk for Outlook, and Ppt for PowerPoint. You can use any names you wish. I kept mine short just to save space here.

Anyway, that's the basic procedure for making the connection to an external program. To review and summarize, the basic procedure is

1. Set a reference to the program's object library in the Reference dialog box.

2. In your code, use a Dim statement to a name that you'll use in code to refer to the program.

3. After the Dim statement, use a Set statement with CreateObject() to open an instance of the program.

You can see examples in the sections that follow where I share data between Microsoft Access, Outlook, Word, and Excel.

Was this article helpful?