Lesson 3: OLE Automation

Lesson Objectives

Upon completion of this lesson, the participant will be able to:

Some Topics to be introduced in this lesson include:

OLE Automation

OLE Automation is the process by which a source application uses object variables or references to control a target application, or documents or other objects supported by the target application. You start by setting an object variable to the target application or some object that the target application supports, using CreateObject, GetObject, or you establish a reference to the target application's Application Object Library (if it has one). Then the object variable in the source macro can use the methods and properties of the corresponding object in the target application.

An application can also use CreateObject and GetObject to refer to itself or one of its subordinate objects. For example the following is both a legal Excel macro and a legal MSProject macro:

Sub Macro1( )

Dim s As Object

Set s = CreateObject("Excel.Sheet")

s.Cells(1, 3).Value = "abc"

s.SaveAs "d:\excel5\class\create2.xls"

End Sub

Note that an application cannot initiate a DDE conversation with itself - see the section on DDE. For example, the following statement is illegal in an MSProject macro:

DDEInitiate "Winproj", "System"

OLE Automation Support

Here's the degree to which Excel, MSProject, Word, and VB3 support OLE Automation:

Excel and MSProject

Excel 5.0 and MSProject 4.0 macros can use all three techniques: CreateObject, GetObject, or establishing a reference to another application's Application Object Library.

Word

Word 6.0 macros cannot use OLE Automation to control other applications, but other applications can use OLE Automation to control it. When other applications use an object variable to control Word, they cannot use the Word named arguments. Word commands can be used by listing the values of arguments without argument names, but you can't skip arguments with consecutive commas (or whatever the list separator is). For example, here's the syntax for the Word Basic command FileOpen, with the first three arguments:

FileOpen .Name = text [,.ConfirmConversions = number] [,.ReadOnly = number]

Both of the following Word lines open the specified file as read only:

FileOpen .Name = "c:\word6\t1.doc", .ReadOnly = 1

FileOpen "c:\word6\test1.doc", 0, 1

The second line above has a dummy 0 for the value of the ConfirmConversions argument - you can't skip it when you don't use the argument names.

The MSProject macro below tells Word Basic to do the same thing as the above. Word does not have to open when the macro runs. If Word is not open before the MSProject macro runs, then Word will automatically close when the MSProject macro ends, because w is a local variable.

Sub Sub1()

Dim w As Object

Set w = CreateObject("Word.Basic")

w.FileOpen "c:\word6\t1.doc", 0, 1

End Sub

DDE actually shines here - you can use the argument names if you use DDEExecute. Word must be open before the MSProject macro below runs.

Sub Sub1()

DDEInitiate "WinWord", "system"

DDEExecute "[FileOpen .Name = ""c:\word6\t1.doc"", .ReadOnly = 1]"

DDETerminate

End Sub

One last warning about using Word Basic commands without argument names - there are a few problems with some commands. For example, the EditFind command requires a bogus dummy string argument between its first and second argument if you don't use argument names:

EditFind .Find = "wow", .Direction = 0 'OK

EditFind "wow", 0 'Doesn't work, but should

EditFind "wow", " ", 0 'Does work, but shouldn't

Visual Basic

Visual Basic 3.0 procedures can use OLE Automation to control other applications, but another application cannot use OLE Automation to control VB3 objects. For example an MSProject macro can't set an object variable to manipulate a text box in a VB3 form. VB3 has some other limitations:

VB3 cannot make references to Application Object Libraries.

VB3 procedures can't use some of the block structures available in Visual Basic for Applications, such as the "For Each" loop and the "With" block.

VB3 procedures cannot used named argument, but must instead list the argument values in order. MSProject behaves inconsistently regarding skipping arguments by using consecutive commas (assuming the list separator is a comma). Skipping arguments by using consecutive commas causes an "Argument value not valid" error for some methods/functions in MSProject macros. Even in cases where it's OK in an MSProject macro, the same statement may cause the error when run from a VB3 procedure.

The following table of syntax examples assumes the object variable x refers to MSProject:

Statement

MSProject

VB3
x.FileOpen Name:="P1.mpp"

Yes

  
x.FileOpen "P1.mpp"

Yes

Yes
x.SelectCell 3, , False

Yes

  
x.SelectCell 3, 4, False

Yes

Yes
x.FilePageSetupFooter "task sheet", _

"left footer", , "right footer"

    
x.FilePageSetupFooter "task sheet", _

"left footer", "", "right footer"

Yes

Yes

Some Terminology

Here's some terminology that will be used in the OLE Automation examples. These terms will become clearer in the examples, and are discussed in greater detail in the "Invisible Objects and Object Lifetime" topic.

Invisible Application: An application that is running but doesn't show in the Windows Task List. An application can be made visible by setting its Visible property to True if it has one. In this context, "visible" means "in the Windows Task List", not necessarily physically visible.

Invisible Document: A document in an invisible application, or a document in a visible application, but the document can't be seen and doesn't appear in the list of open windows. An invisible document can be "more hidden" than a "hidden" document. As you'll see later, an MSProject invisible project might not show up in the Window Unhide list, but will show in the Window New Window list.

Reference-Dependent Object: An open object that exists only as long as it or one of its subordinate objects is being referenced.

As you'll see later, some objects can be toggled between invisible and visible. Also, some actions can cause a reference-dependent object to become reference-independent.

Here's some things that can cause an object variable to stop pointing to an object:

The object variable was set to some other object

The object variable was set to Nothing, for example Set x = Nothing

The object variable was a local variable and the macro has ended

The END macro command was executed (not by choosing the menu command Run End)

The menu command Run, Reset was used

Note: The last two items apply to clearing the value of any variable.

As you experiment with CreateObject and GetObject, you may end up running invisible copies of applications. The following macros can be used to make MSProject and Excel visible. The way GetObject is used in these macros requires that MSProject and Excel already be running (visible or not). The On Error Resume Next statements prevent error messages if they are not running. The GetObject function is discussed in detail later in this lesson. There are also API calls that can be used to make applications visible - see Lesson 8.

Try This

(A) The Excel macro below makes MSProject visible and active if it is running (and not busy).

1. Get into Excel and create an new module called OLE (From the Insert menu, choose Macro, and then Module).

2. Enter the following macro:

Sub ShowMSProject()

Dim oMSP As Object

On Error Resume Next

Set oMSP = GetObject(, "MSProject.Application")

If Err > 0 Then

MsgBox "MSProject is busy or not running."

Else

oMSP.Visible = True

AppActivate "Microsoft Project"

End If

End Sub

3. Save the workbook.

4. Start MSProject, switch to Excel and run the ShowMSProject macro. MSProject should become active. As you'll see later, this macro can make MSProject active even if it were originally invisible.

(B) The MSProject macro below makes the youngest instance of Excel visible and active if it is already running (and not busy).

1. Get into MSProject and create an new module called OLE.

2. Enter the following macro:

Sub ShowExcel()

Dim oXL As Object

On Error Resume Next

Set oXL = GetObject(, "Excel.Application")

If Err > 0 Then

MsgBox "Excel is busy or not running."

Else

oXL.Visible = True

AppActivate "Microsoft Excel"

End If

End Sub

3. Start a new instance of Excel, switch to MSProject and run the ShowExcel macro. The instance of Excel should become active. As you'll see later, this macro can make the youngest instance of Excel active even if it were originally invisible.

CreateObject (class)

Returns a reference to an object determined by the class argument. Depending on class, the reference may be to an existing object or to a newly created object. The object reference returned by CreateObject is usually assigned to an object variable.

Class

A string of the form "appName.objectType". Here are some of the values available for MSProject, Excel, and Word:

Note that Word doesn't have a Word.Application object. However, the Word.Basic object is like an application object. If Word is started as a result of CreateObject or GetObject then when the object variable ceases to exist, Word automatically closes all documents and the Word application without warnings.

Many of the following examples use x as the object variable, but any legal object variable name could be used.

Using CreateObject With MSProject Objects

Set x = CreateObject("MSProject.Application")

Refers to an existing instance if MSProject is running, otherwise it starts an invisible reference-dependent instance with no open projects. Does not activate MSProject. The x refers to MSProject.Application.

Note that a reference-dependent instance of MSProject becomes reference-independent if you open any projects with methods like FileNew or FileOpen.

Global.mpt Concerns: Project looks in the current directory and in the winproj.exe directory for Global.mpt, not in the working directory. If can't find, it creates one in the executable directory without warning.

Try This

(A) The Excel macro below starts MSProject, and makes it visible:

1. Make sure MSProject is not running.

2. Enter the following macro in Excel and run it.

Dim x As Object 'In the declaration area.

Sub Sub1a()

Set x = CreateObject("MSProject.Application")

x.Visible = True

End Sub

3. Switch to MSProject. You should see no open projects.

4. Switch back to the Excel macro and from the Run menu, choose Reset.

5. Check the Windows Task List. MSProject should not be listed. Is it running invisibly or just not running at all? Did the macro start a reference-dependent or reference-independent instance of MSProject.

(B) The Excel macro below starts MSProject invisibly, creates a new project, and adds a new task to it.

1. Make sure MSProject is not running.

2. Enter the following macro in Excel and run it.

Sub Sub1b()

Dim x As Object

Set x = CreateObject("MSProject.Application")

x.FileNew

x.ActiveProject.Tasks.Add Name:="wow"

End Sub

3. Check the Windows Task List. Microsoft Project should not be in the list.

4. Start MSProject manually in Program Manager. The new project and task created by the macro should be there. Is this instance of MSProject reference-dependent?

5. Close MSProject and don't save changes to the new project.

6. Change the macro so that x is declared as a module-level variable, i.e. move the Dim statement to the declaration area of the Excel module. Also, add the line x.Visible=True before the line x.FileNew.

7. Run the macro again.

8. Try to close MSProject - you should get a warning that MSProject is being used by another application. Cancel the message. Why didn't this message come up the first time you ran the macro?

9. Switch back to the Excel macro and from the Run menu, choose Reset.

10. Switch to MSProject and close it. Why no message this time?

Set x = CreateObject("MSProject.Project")

Starts an invisible reference-dependent instance of MSProject, if it's not running. It then creates a new invisible project. The x refers to the new project, and x.Parent refers to MSProject.Application. The new project is reference-dependent, unless the command executed as an MSProject command.

If MSProject is running visibly, then the name of the invisible project isn't listed in the Window menu's open project list or in the Window Unhide list, but is listed in the Window New Window list. You can use Window New Window to make the invisible project become visible.

Try This

The MSProject macro below creates two new invisible projects and displays the names of all the "open" projects.

1. Enter the following macro in MSProject and run it.

Sub Sub2a()

Dim p As Object, p1 As Object, p2 As Object

Set p1 = CreateObject("MSProject.Project")

Set p2 = CreateObject("MSProject.Project")

For Each p In Projects

MsgBox p.Name

Next

End Sub

2. After the macro ends, check the MSProject Window menu, and you can see that the two new projects are not listed as open or hidden. Use Window New Window to make the projects visible, and then close them. Were these two new projects reference-dependent?

3. Paste the above macro into an Excel module. Replace the word "Projects" in the "For Each ..." line to "p1.Parent.Projects" (without the quotes). Note that p1.Parent refers to MSProject.

4. Run the macro. Switch to MSProject. Verify that the two new projects are not in the Window New Window list. Do you think the two projects are invisible or just not open at all? Were the two created projects reference-dependent?

Using CreateObject With Excel Objects

Set x = CreateObject("Excel.Application")

Always starts a new invisible reference-independent instance of Excel. The x refers to the Excel.Application object of the new instance.

Set x = CreateObject("Excel.Sheet")

Starts an invisible reference-dependent instance of Excel if it's not running, otherwise it uses the *youngest instance. It creates a new worksheet "Sheet1" in a new reference-dependent workbook called "Object" (or "Object 2" etc.). The x refers to Sheet1, x.Parent to the workbook, and x.Parent.Parent to Excel.Application. If Excel is visible, then the new workbook is visible, and Excel is reference-independent.

*If there are existing instances, youngest means the one that was started last.

Try This

The MSProject macro below creates two new workbooks, and puts the time in cell A1 of Sheet1 of each workbook.

1. Make sure Excel is not running.

2. Enter the following macro in MSProject and run it.

Dim s1 As Object, s2 As Object 'In the declarations area.

Sub Macro1()

Set s1 = CreateObject("Excel.Sheet")

Set s2 = CreateObject("Excel.Sheet")

s1.Cells(1, 1).Value = Format(Now, "mm:ss")

s2.Cells(1, 1).Value = Format(Now, "mm:ss")

s1.Parent.Parent.Visible = True

End Sub

3. Check the names of the workbooks and the times in cell Sheet1!A1 of each workbook. Close the workbooks "Object" and "Object 2". What does s1.Parent.Parent.Visible refer to?

4. Manually use File New to create another workbook in Excel and type "hey" in Sheet1!A1.

5. Leave Excel running. Switch back to MSProject and run the macro again. Note that it did not activate the Excel window this time - setting the Visible property to True only activates Excel if it is not in the Windows Task List.

6. Switch to Excel. Check the workbook names again. Did the macro create a new instance of Excel the second time it was run?

7. Leave Excel running. Switch back to MSProject and from the Run menu choose Reset. Switch to Excel. Are the workbooks created by the macro still there? Did the macro create reference-dependent workbooks?

If s1 and s2 had been declared locally, would you have seen the workbooks created by the macro after the macro ended?

Set x = CreateObject("Excel.Chart")

Starts an invisible reference-dependent instance of Excel if it's not running, otherwise it uses the youngest instance. It creates a new worksheet "Sheet1" and chart sheet "Chart1" in a new reference-dependent workbook called "Object (or "Object 2" etc.). Chart1 is based on Sheet1 which Excel populates with sample data. The x refers to Chart1, x.Parent to the workbook, and x.Parent.Parent to Excel.Application. If Excel is visible, then the new workbook is visible, and Excel is reference-independent.

Question: How can you use x to refer to Sheet1?

Try This

The MSProject macro below creates a new workbook with one worksheet and one chart sheet.

1. Enter the following macro in MSProject and run it.

Dim oChart As Object 'In the declarations area.

Sub Macro1()

Set oChart = CreateObject("Excel.Chart")

oChart.Parent.Parent.Visible = True

End Sub

2. Switch to Excel. Where is the chart getting its data from? How can you find out whether the workbook created is reference-dependent?

Using CreateObject With Word Objects

Set x = CreateObject("Word.Basic")

If Word is not running, then a visible reference-dependent instance of Word is started with focus and with no open documents, otherwise x references the youngest existing instance of Word without activating it. The x refers to Word.Basic.

Try This

The MSProject macro below creates a new Word document, enters some text, and saves the document.

1. Make sure Word is not running.

2. Enter the following macro in MSProject and run it.

Sub w1()

Dim w as object

Set w = CreateObject("Word.Basic")

w.FileNew

w.Insert "hello"

w.FileSaveAs "c:\temp.doc"

End Sub

3. Is Word still running after the macro ends? Was the instance of Word created reference-dependent? If w had been declared as a module-level variable, would Word remain open after the macro ends? Open c:\temp.doc to see if it was created and saved correctly. Close temp.doc. Create a new doc and enter some text.

4. Leave Word running. Switch to MSProject and change "hello" to "wow" in the macro and run it again. Is Word still running? Did it overwrite c:\temp.doc? Any overwrite warning? Did it use the existing instance of Word or start a new instance?

GetObject ([pathname][,class])

Returns a reference to an object determined by the arguments. Depending on the arguments, the reference may be to an existing object or to a newly created object. The object reference returned by CreateObject is usually assigned to an object variable.

pathname

The path and file name of a document file.

class

Has the same meaning as in CreateObject.

You must provide one or the other or both of the arguments.

You don't need to use the class argument if you give a document name. If you provide a document pathname but not a class, then it finds the correct application if the system associations are correctly setup, and then uses the default class for the application.

If you use an empty string for pathname then GetObject("", class) behaves like CreateObject(class).

If you leave out pathname, then GetObject(, class) usually requires class to be an application object and the application to be running.

If you provide both a document pathname and a class, then the specified document must support that class. For example, the first statement below is legal and the second is not:

Set x = GetObject("c:\winproj\p1.mpp", "MSProject.Project") 'OK

Set x = GetObject("c:\winproj\p1.mpp", "MSProject.Application") 'Not OK

 

Using GetObject With MSProject Objects

Set x = GetObject(, "MSProject.Application")

MSProject must be running, otherwise an OLE Automation error occurs. The x refers to MSProject Application.

Set x = GetObject("", "MSProject.Application")

Behaves like: Set x = CreateObject("MSProject.Application").

Set x = GetObject("", "MSProject.Project")

Behaves like: Set x = CreateObject("MSProject.Project")

Set x = GetObject("c:\wprj4dat\p1.mpp")

Set x = GetObject("c:\wprj4dat\p1.mpp", "MSProject.Project")

Each of these starts an invisible reference-dependent instance of MSProject, if it's not running. If the specified MPP file is not open, then it is opened invisibly, and is reference-dependent unless the command was executed as an MSProject command. The x refers to the project, and x.Parent refers to MSProject.Application.

These are ILLEGAL :

Set x = GetObject(, "MSProject.Project")

Set x = GetObject("c:\wprj4dat\p1.mpp", "MSProject.Application")

Using GetObject With Excel Objects

Set x = GetObject(,"Excel.Application")

Excel must be running, otherwise an OLE Automation error occurs. The x refers to

Excel.Application for the youngest instance of Excel.

Set x = GetObject("","Excel.Application")

Behaves like: Set x = CreateObject("Excel.Application").

Set x = GetObject("","Excel.Sheet")

Behaves like: Set x = CreateObject("Excel.Sheet")

Set x = GetObject("c:\Excel\test.xls")

Set x = GetObject("c:\Excel\test.xls", "Excel.Sheet")

Each of these starts an invisible reference-independent instance of Excel, if it's not running, otherwise it uses the youngest existing instance. If the specified XLS file is not open, then it is opened as a hidden workbook, and is reference-dependent unless the command was executed as an Excel command. The x refers to the activesheet in the specified file.

Set s = GetObject("","Excel.Chart")

Behaves like: Set s = CreateObject("Excel.Chart")

Set s = GetObject("c:\Excel\test.xls", "Excel.Chart")

Behaves like: GetObject("c:\Excel\test.xls") except that the workbook must contain at least one chart sheet.

These are ILLEGAL :

Set s = GetObject(,"Excel.Sheet")

Set s = GetObject("c:\Excel\test.xls", "Excel.Application")

Using GetObject With Word Objects

Set w = GetObject("", "Word.Basic")

Behaves like w = CreateObject("Word.Basic")

Set d = GetObject("d:\word6\test.doc")

Doesn't give an error message, but you can't use it for anything.

These are ILLEGAL:

Set w = GetObject(, "Word.Basic")

Set w = GetObject("d:\word6\test.doc", "Word.Basic")

CreateObject and GetObject - Review

There are many exotic ways to use CreateObject and GetObject, but only a few variations are commonly used. First of all, the application you want to control is usually already running visibly, and there usually is just one instance. Also, you usually set the object variable to refer to the application, not to a subordinate object in the application, like a sheet or chart. To work with other subordinate objects in the application, you work your way down the object ladder.

The table on the next page summarizes the syntax variations of CreateObject and GetObject. The most commonly used forms are highlighted.

Set x = Result
CreateObject("MSProject.Application")

GetObject("","MSProject.Application")

 Refers to an existing instance if MSProject is running, otherwise it starts an invisible reference-dependent instance with no projects open. The x refers to MSProject.Application.
CreateObject("MSProject.Project")

GetObject("","MSProject.Project")

 Starts an invisible reference-dependent instance of MSProject, if it's not running. It then creates a new invisible project. The x refers to the new project, and x.Parent refers to MSProject.Application. The new project is reference-dependent, unless the command executed as an MSProject command.
CreateObject("Excel.Application")

GetObject("","Excel.Application")

 Always starts a new invisible reference-independent instance of Excel. The x refers to the Excel.Application object of the new instance.
CreateObject("Excel.Sheet")

GetObject("","Excel.Sheet")

 Starts an invisible reference-dependent instance of Excel if it's not running, otherwise it uses the youngest instance. It creates a new worksheet "Sheet1" in a new reference-dependent workbook called "Object" (or "Object 2" etc.). The x refers to Sheet1, x.Parent to the workbook, and x.Parent.Parent to Excel.Application. If Excel is visible, then the new workbook is visible and Excel is reference-independent.
CreateObject("Excel.Chart")

GetObject("","Excel.Chart")

 Starts an invisible reference-dependent instance of Excel if it's not running, otherwise it uses the youngest instance. It creates a new worksheet "Sheet1" and chart sheet "Chart1" in a new reference-dependent workbook called "Object (or "Object 2" etc.). Chart1 is based on Sheet1 which Excel populates with sample data. The x refers to Chart1, x.Parent to the workbook, and x.Parent.Parent to Excel.Application. If Excel is visible, then the new workbook is visible and Excel is reference-independent.
CreateObject("Word.Basic")

GetObject("", "Word.Basic")

 If Word is not running, then a visible reference-dependent instance of Word is started with focus and with no open documents, otherwise it activates the youngest existing instance. The x refers to Word.Basic.
GetObject(,"MSProject.Application") MSProject must be running, otherwise an OLE Automation error occurs. The x refers to MSProject Application.
GetObject("c:\data\p1.mpp")

GetObject("c:\data\p1.mpp", _ MSProject.Project")

 Starts an invisible reference-dependent instance of MSProject, if it's not running. If the specified MPP file is not open, then it is opened invisibly, and is reference-dependent unless the command was executed as an MSProject command. The x refers to the project, and x.Parent refers to MSProject.Application.
GetObject(,"Excel.Application") Excel must be running, otherwise an OLE Automation error occurs. The x refers to Excel.Application for the youngest instance of Excel.
GetObject("c:\data\test.xls")

GetObject("c:\data\test.xls", _

"Excel.Sheet")

 Each of these starts an invisible reference-independent instance of Excel, if it's not running, otherwise it uses the youngest existing instance. If the specified XLS file is not open, then it is opened as a hidden workbook, and is reference-dependent unless the command was executed as an Excel command. The x refers to the activesheet in the specified file.
GetObject("c:\data\test.xls", _

"Excel.Chart")

 Behaves like GetObject("c:\data\test.xls") except that the workbook must contain at least one chart sheet in the workbook.

USING APPLICATION OBJECT LIBRARIES

Excel and MSProject have their own Application Object Libraries. Other applications may have their own libraries in the future.

Advantages of using object libraries:

You don't need to set an object variable with either CreateObject or GetObject. Your macro can use the predefined object names that refer to the target application, for example, Excel and MSProject (although in some cases you must use Excel.Application and MSProject.Application).

Macros in the source application can use the named constants from the target Application Object Library. For example, if Excel has a reference to the Microsoft Project 4.0 Object Library, then Excel macros would automatically understand constants like pjDuration.

Disadvantages of using object libraries:

Once a macro in a source application references a target application library, the target application might continue to run invisibly after the source macro ends, and even after the source application closes.

If you close the target application after if has been referenced by a source macro, then the source application may have trouble referencing the library again until you do one of the following in the source application.

Choose the menu command Run Reset.

Execute the End command in the Debug window or in a macro.

Uncheck the reference to the Object Library, close the dialog, get back into it, and recheck the reference.

Exit and restart the source application.

Microsoft Project 4.0 Object Library

To use the Microsoft Project 4.0 Object Library from Excel, get into an Excel module and choose Tools, References, and check: Microsoft Project 4.0 Object Library. Then you don't need CreateObject or GetObject to refer to MSProject. You can just use the name MSProject or MSProject.Application to refer to MSProject. You could also introduce another object variable with a shorter name, for example:

Set x = MSProject.Application

When an Excel macro references MSProject, it refers to an existing instance if MSProject is already running, otherwise it starts a new invisible instance with no open projects. In the latter case, MSProject continues to run until you do one of the following:

Choose the Excel menu command Run Reset.

Execute the End command in the Debug window or in an Excel macro.

Uncheck the reference to the Microsoft Project 4.0 Object Library in Excel, close the dialog, get back into it, and recheck the reference.

Exit Excel and restart it.

Manually close MSProject or tell it to Quit via a macro.

Also, if you close MSProject after an Excel macro has referenced it, then Excel macros can't reference MSProject again until one of the above actions is taken.

Try This

The Excel macro below starts MSProject if not already running, makes it visible and active, and creates a new visible project.

1. Enter the following Excel macro and run it.

Sub Sub4()

MSProject.Application.Visible = True

AppActivate "Microsoft Project"

MSProject.FileNew

End Sub

2. Close MSProject (choose Yes to any warning message).

3. Run the macro again. Did you get an error message? From the Run menu, choose Reset, and run the macro again.

Excel 5.0 Object Library from MSProject

To Use the Excel 5.0 Object Library from MSProject, get into the module editor and choose Tools, References, and check Excel 5.0 Object Library. Then you don't need CreateObject or GetObject to refer to Excel. You can just use the name Excel or Excel.Application. You could also use another object variable with a shorter name, for example:

Set x = Excel.Application.

The first time an MSProject macro references Excel, a new invisible instance is run, and all later references use that same instance. That instance continues running until you manually close it or use a macro to tell it to Quit. Note that there isn't a way to reference an existing instance of Excel using this library technique. If you need to reference an existing instance, then use GetObject.

Also, if you close Excel after an MSProject macro has referenced it, then MSProject macros can't reference Excel again (via the library reference) until one of the following actions is taken in MSProject:

Uncheck the reference to the Excel 5.0 Object Library, close the dialog, get back into it, and recheck the reference.

Execute the End command in the Debug window or in an MSProject macro.

Choose the menu command Run Reset.

Exit MSProject and restart it.

Note: Unlike MSProject, when you close Excel, it does not give a warning that another application is using Excel.

Invisible Objects and Object Lifetime - Review

This section reviews and formalized the concepts of reference-dependent objects and opening applications and documents invisibly.

Invisible Application:

An application that is running but doesn't show in the Windows Task List. This is not the same as being minimized or hidden under other windows. If a macro creates an invisible instance of an application, the macro may need to close the application or make it visible before the macro ends, to avoid having invisible instances eating up resources.

If x refers to MSProject.Application, then MSProject can be made visible by using x.Visible=True, x.AppMaximize, x.AppMinimize, or x.AppRestore. The last three statements always activate MSProject, but x.Visible=True makes MSProject active only if MSProject isn't already visible. The statement x.Visible=False has no effect on MSProject.

If x refers to Excel.Application, then x.Visible=True makes Excel visible, and x.Visible=False makes it invisible. Using x.Visible=True when Excel is already visible does not activate Excel.

Microsoft Word can't be made invisible using OLE Automation.

Invisible Document:

A document that is in an invisible application, or a document in a visible application, but the document can't be seen and doesn't appear on the list of open files at the bottom of the application's Window menu. If the application is visible, the document might be invisible because of the method used to open it, or because it was made hidden by some command after it was opened.

If you use CreateObject or GetObject to create/open a project in MSProject, then the project is invisible.

If you use CreateObject or GetObject to create/open a sheet or chart in a new invisible instance of Excel, then the associated workbook is invisible. If Excel is then made visible, the workbook is still a hidden workbook.

There are no invisible documents in Word.

Try This

These Excel macros illustrates when an invisible project can or cannot be the active project.

1. Make a test project with two tasks named T1 and T2 and save it as "c:\p1.mpp".

2. Close MSProject.

3. Enter the following Excel macro and run it.

Sub Macro1( )

Dim x As Object, p As Object

Set x = CreateObject("MSProject.Application") 'Starts invisible

x.FileOpen "c:\p1.mpp" 'Open p1.mpp

Set p = x.ActiveProject 'Set object variable

p.Tasks(2).Name = "zawa" 'Change task2 name

x.Alerts False 'Save without asking

x.Quit 'Close MSProject

End Sub

4. Check that the name of task 2 has changed to zawa.

5. The following Excel macro won't work. Enter and run it. Choose OK to the MSProject error message, switch back to Excel, and choose Goto in the Excel error message. What's wrong with this macro?

Sub Macro2()

Dim x As Object, p As Object

Set p = GetObject("c:\p1.mpp")

Set x = p.Parent

x.Visible = True

p.Tasks(2).Name = "zawa"

x.FileSave

x.Quit

End Sub

6. Here' a quick fix for the above macro. Add the following three lines before the x.FileSave line, and run the macro again. Which application's messages is the "On Error" line supposed to prevent? Which application's messages is the "x.Alert" line supposed to prevent?

On Error Resume Next

x.Alerts False

x.FileOpen "c:\p1.mpp"

Reference-dependent Object:

An open object that exists only as long as it or one of its subordinate objects is being referenced. The object could be an application or a subordinate object that the application supports, like a project in MSProject, or a worksheet in Excel, or Word Basic in Word.

If you run an MSProject macro that starts Word as a result of

x=CreateObject("Word.Basic")

then that instance of Word.Basic is reference-dependent, because if the value of x changes or x ceases to exist, then Word will automatically close without warning, and without saving changes to documents.

The next two definitions are specific cases:

Reference-dependent document:

An open document that exists only as long as it or one of its subordinate objects is being referenced. When a reference-dependent document closes automatically, it typically does not automatically save changes or ask if you want to save changes.

For example, if you run an EXCEL macro that opens a project using

Set p=GetObject("c:\winproj\p1.mpp")

then p1.mpp is reference-dependent, because if the value of p changes or p ceases to exist, then p1.mpp automatically closes without warning and without saving changes.

NOTE: If you run the above command in an MSProject macro instead of an Excel macro, then p1.mpp is not reference-dependent. I.e. even after p ceases to exist, p1.mpp is still open - it's invisible, but can be seen on the Window New Window list.

Similarly, if you run an MSProject macro that opens a worksheet using

Set s = GetObject("c:\Excel\test.xls")

then test.xls is reference-dependent, because if the value of s changes or s ceases to exist, then test.xls automatically closes. Note that test.xls opens as a hidden workbook and will become reference independent if unhidden manually or by executing

s.Parent.Parent.Windows("test.xls").Visible = True

Reference-dependent projects are created/opened as a result of CreateObject or GetObject run from another application like Excel, and are invisible at first. They can be made visible through the Window New Window menu command if it's available. Invisible reference-dependent projects can never be the active project.

Reference-independent projects are most recently created/opened with methods other than CreateObject or GetObject, i.e. like FileNew and FileOpen.

Technically there are no reference-dependent Word documents because Word doesn't have a Word.Document object. However, the Word.Basic object can be reference-dependent and if the Word.Basic object closes, it closes the application and all open documents without warning.

Reference-dependent Application Instance:

An instance of an application that exists only as long as it or one of its subordinate objects is being referenced.

MSProject can be reference-dependent only if there are no open reference-independent projects, and if MSProject was started as a result of CreateObject/GetObject or a reference via the Microsoft Project 4.0 Object Library.

For example, if MSProject is not running, then starting it with

Set x=CreateObject("MSProject.Application")

creates a reference-dependent instance of MSProject, because if the value of x changes or x ceases to exist, then MSProject closes automatically.

If MSProject is started as the result of CreateObject or GetObject, then if starts out dependent on the object variable used in the CreateObject or GetObject command. After that, it can become reference-independent by restarting it manually, or by opening reference-independent projects.

There are no visible reference-dependent instances of Excel. Once an instance of Excel shows up in the Windows Task List, it remains running until you manually close it or use some command like the Quit method. Invisible instances may or may not be reference dependent. CreateObject("Excel.Application") starts reference-independent instances. CreateObject("Excel.Sheet") and CreateObject(Excel.Chart") create reference-dependent instances if Excel was not already running.

Technically Word can't be a reference-dependent application because there is no

Word.Application object, but the Word.Basic object can be reference-dependent and behaves like an application object.

Using VB3 Forms With MSProject

Because Visual Basic for Applications in MSProject does not include rich event driven graphical interface objects such as text boxes, dropdown lists, etc., some developers will choose to create custom applications using VB3 and MSProject. Two possible design philosophies are listed below

Design it strictly as a VB3 program that controls MSProject. No MSProject macro code is used at all. Advantage: Simplicity.

Write most of the code in MSProject macros. Any "fancy" input/output dialogs are designed in small VB3 programs that are called from the MSProject macros. Advantage: Speed.

The second approach requires some way for the VB3 program to return user input to the calling MSProject macro. The are several ways to pass data between VB3 and MSProject macros, including: OLE Automation, DDE, and API calls (for example, passing data via a global memory block).

Try This

This example illustrates a VB3 program returning input to MSProject via OLE Automation, using the Text9 and Text10 fields of the project summary task of the active project. The purpose of the example is for illustration only - the function it performs could be done without VB3. Here's a short description of the logic:

The MSProject macro warns the user that the Text9 and Text10 fields of the project summary task will be modified and allows the user to cancel the macro.

If the user chooses to continue, the MSProject macro sets the project summary Text10 field to blank.

The MSProject macro uses Shell to start the VB3 program AddTask.exe.

The MSProject macro waits in a DoEvents loop until the project summary Text10 field is no longer blank. During this time, in the VB3 form, the user can enter the name for a new task in a textbox and then choose OK or Cancel. The VB3 program returns "ok" or cancel" in the project summary Text10 field, and, if OK was chosen, returns the new task name in the project summary Text9 field.

The MSProject macro assigns the returned values (the project summary Text9 and Text10 fields) to variables sTextEntered and sButtonChosen. If sButtonChosen = "ok", then a new task is added to the active project with the new name that the user entered.

Here are the detailed steps:

1. Start VB3 (Microsoft Visual Basic 3.0). If the toolbox isn't showing, then from the Window menu choose Toolbox. Resize Form1 as shown in the picture. Press F4 and change the Caption property to "Add Task".

 

2. Create the textbox shown in the picture (use the tool). Change its Name property to txtTextBox and change its Text property to blank (so it's empty).

3. Create the OK command button as shown in the picture (use the tool). Change its Name property to cmdOK, and change its Caption property to "OK". Double click the button and enter the body of the cmdOK_Click procedure shown below:

Sub cmdOK_Click ()

Dim oMSP As object

Set oMSP = GetObject(, "MSProject.Application")

oMSP.ActiveProject.Text9 = txtTextBox.Text

oMSP.ActiveProject.Text10 = "ok"

End

End Sub

4. Create the command button that says "Cancel" as shown in the picture. Change its Name property to cmdCancel, and change its Caption property to "Cancel". Double click the button and enter the body of the cmdCancel_Click procedure shown below:

Sub cmdCancel_Click ()

Dim oMSP As object

Set oMSP = GetObject(, "MSProject.Application")

oMSP.ActiveProject.Text10 = "cancel"

End

End Sub

5. In MSProject, create a test project, turn on the option to view the project summary task (in the Tools Options View dialog).

6. In VB3, press F5 to test run the program. If the form pops up successfully, enter a new task name in the textbox. Test both the OK and Cancel buttons. After each test, switch to MSProject and verify that Text9 and Text10 of the project summary task have been assigned the appropriate values. It won't change Text9 if Cancel is chosen. If you get runtime errors, use the Run menu End command, and fix the errors.

7. Switch to File Manager and create the directory C:\AddTask. Switch back to VB3. From the File menu, choose "Make Exe File ..." and save it as C:\AddTask\AddTask.exe. Next, from the File menu choose "Save Project", and save everything to the C:\AddTask directory, accepting the default file names. Finally, exit VB3.

8. Enter and test run the MSProject macro below:

Sub AddTask()

Dim sTextEntered As String

Dim sButtonChosen As String

Dim nAnswer As Integer

nAnswer = MsgBox("This macro uses Text9 and Text10 of the " & _

"active project summary task. Continue?", vbOKCancel)

If nAnswer = vbCancel Then End

ActiveProject.Text10 = ""

Shell "C:\AddTask\AddTask.exe", 1 'start as normal window

Do

DoEvents

Loop Until ActiveProject.Text10 <> ""

sTextEntered = ActiveProject.Text9

sButtonChosen = ActiveProject.Text10

If sButtonChosen = "ok" Then

ActiveProject.Tasks.Add Name:=sTextEntered

End If

End Sub

Lesson 3 Exercises

  1. The VB3 program described below displays the name and duration of one task at a time from the active project in a VB3 form, and has an option to update the duration. It also has buttons to move to the previous or next task id. Some error checking is added in Step (13). Follow the steps below.


    1. Start VB3 (Microsoft Visual Basic 3.0). If the toolbox isn't showing, then from the Window menu choose Toolbox. Resize Form1 as shown in the picture.


    2. Create the label that says "# of Tasks" as shown in the picture (click on the tool bar and draw a rectangle on Form1). With the label selected, press F4 and change the Caption property to "# of Tasks". Likewise create the labels that say "Id", "Name", and "Duration".


    3. Create the label to the right of "# of Tasks". Change its Caption property to a blank and change its Name property to lblNumOfTasks. Similarly, create the labels to the right of "Id" and "Name", with Name properties lblTaskID and lblTaskName.


    4. Create a text box to the right of "Duration" as shown on the picture (use the tool). Change its Name property to txtDuration and its Text property to blank.


    5. Create the command button that says "Close" as shown in the picture (use the tool). Change its Name property to cmdClose, and Caption property to "Close". Similarly, create the "Previous", "Next", and "Update" buttons with Name properties cmdPrevious, cmdNext, and cmdUpdate, and Caption properties "Previous", "Next", and "Update".


    6. Double click on any part of Form1. Press F2. From the Procedures list, double click on "(declarations)". Use the Dim command to dimension object variables oMSP and oTask, and integer variables nTaskID and nNumOfTasks.


    7. Double click on an empty part of the form to get to the Form_Load event procedure. Enter the following event procedure (the Sub and End Sub are already there). When Form1 is loaded, this will display the # of tasks, and the id, name and duration of the task with Id = 1 (assuming it's not blank).


      8. Sub Form_Load ()
      Set oMSP = GetObject(, "MSProject.Application")
      nNumOfTasks = oMSP.ActiveProject.Tasks.Count
      lblNumOfTasks = nNumOfTasks
      nTaskID = 1
      lblTaskID = nTaskID
      Set oTask = oMSP.ActiveProject.Tasks(nTaskID)
      lblTaskName = oTask.Name
      txtDuration = oTask.Duration
      End Sub

    8. Double click each of the buttons on the form, one at a time, to get to each button click event procedure and enter the code shown below.


      9. 'This will update the task duration with the text from the txtDuration text box.

      Sub cmdUpdate_Click ()
      oTask.Duration = txtDuration
      End Sub

      'This will close the VB3 program.

      Sub cmdClose_Click ()
      End
      End Sub

      'This will move to the previous task id unless it is already at 1.

      Sub cmdPrevious_Click ()
      If nTaskID > 1 Then nTaskID = nTaskID - 1
       
      lblTaskID = nTaskID
      Set oTask = oMSP.ActiveProject.Tasks(nTaskID)
      lblTaskName = oTask.Name
      txtDuration = oTask.Duration
      End Sub

      'This will move to the next task id unless is at the largest id in the project.

      Sub cmdNext_Click ()
      If nTaskID < nNumOfTasks Then nTaskID = nTaskID + 1
       
      lblTaskID = nTaskID
      Set oTask = oMSP.ActiveProject.Tasks(nTaskID)
      lblTaskName = oTask.Name
      txtDuration = oTask.Duration
      End Sub

    9. Switch to MSProject and set the default duration units to "days" (Tools Options Schedule tab, Show Duration In). Create a test project with tasks T1, T2, and T3 each with duration 1d.


    10. Switch to VB3 and move Form1 if necessary so you can see the name and duration fields in the background. Press F5 to run the VB3 program. When Form1 loads, T1 data should be displayed in the form. Test the Previous and Next buttons, but not the Update button yet.


      11. IMPORTANT: If you get runtime error messages, then choose End from the Run menu, check your work, and make corrections.

    11. Display T1 again in the form. The duration is 480 in the textbox (the 480 is the duration in minutes). Enter 960 in the duration text box and choose Update. The duration of T1 in the project changes to 960d. Why? Enter 2d in the duration text box and choose Update. The duration of T1 should become 2d. Why? Close Form1 and change the duration of T1 back to 1d.


      12. Here's a review of the rules used when setting the duration property of a task to a value:

    12. In the Form_Load, cmdPrevious_Click, and cmdNext_Click procedures, replace:


      13. txtDuration = oTask.Duration
      with:
      txtDuration = oTask.GetField(29)
      The easiest way to do this is to double click on an empty part of the form and then use the menu command Edit Replace. The 29 is from the pjTaskDuration constant listed in the help topic on the GetField method. Test the VB3 program again.

    13. Add error trapping code if necessary so that the VB3 program sets lblTaskName and txtDuration to the empty string "" if the row at nTaskId is blank, i.e. if oTask is Nothing. Also, if oTask is Nothing, the Update_Click procedure will display the message "Task is blank" and then put "" in the txtDuration textbox. Don't bother trapping bad values entered in the txtDuration textbox.


    14. Add another command button labeled GoTo that lets the user enter a task id in an input box, and then displays the id, name, and duration for that task. Start with On Error Resume Next to trap bad id numbers. Assign the value returned by the InputBox to a local integer variable N and then use Set oTask = oMSP.ActiveProject.Tasks(N). If an error occurred (Err > 0) then give a message saying "Bad Id" and then exit the sub. Otherwise, Assign N to nTaskId and update lblTaskID, lblTaskName, and txtDuration as in the other procedures (checking for blank rows).


  2. Write an MSProject macro that creates a new invisible project, adds three tasks to it called "a", "b", and "c", and link the tasks in order with FS relationships. The project is invisible the whole time.


    3. Note: You can't activate the invisible project or apply a view or select any tasks in a table or use the link tool button. Use CreateObject to create the project. If P is the object variable that refers to the new invisible project, then you can use the Add method several times to add the three tasks, for example, P.Tasks.Add Name:="a". Then you can put the appropriate values in the Predecessors field for tasks 2 and 3, for example, P.Tasks(2).Predecessors=1.

    After you run the macro, look on the Window menu to see what projects are open. Is the Unhide item available on the Window menu? Choose New Window from the Window menu and make the invisible project show up in a new window. Are the tasks and predecessors correct?

  3. Write an Excel macro that uses the data on Sheet1 of the active Excel workbook to create a new project. Columns A, B, and C on Sheet1 contain a list of task names, starting dates, and durations, for example Sheet1 might contain the following in cells A1:C4


    4. T1 11/7/94 5d
    T2 11/3/94 2w
    T3 11/14/94 16h
    T4 11/9/94 3d

    The macro can assume that the data starts in cell A1 and that there are no blank rows, but has to calculate how many rows there are. The macro should do the following steps:

    Start MSProject if it's not running: Use CreateObject and assign the returned value to an object variable called oMSP (so oMSP refers to MSProject).

    Make MSProject visible and active.

    Create a new visible project (use MSProject's FileNew method). Later, the new project can be referenced as oMSP.ActiveProject.

    Assign the number of tasks on Sheet1 to a variable, for example

    N = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Rows.Count

    For R = 1 to the number of tasks on Sheet1: Use the Add method (with parenthesis around the Name argument) to add another task to the project - use the appropriate cell value for the Name argument, and assign the return value from the Add method to an object variable T (so T refers to the new task). Then use T to assign the appropriate sheet values to the start and duration of the new task.

    Use OLE Automation where possible, and avoid copy/paste techniques.

    Notes:

    In Excel, to access the content of a cell at row R column C of Sheet1, use can use the expression: Sheets("Sheet1").Cells(R,C).Value.

    In the For loop, R will be the loop variable, and C will be 1, 2, or 3, depending on whether you want the name, start, or duration.

    To add a new task at row R in the new project and have the object variable T refer to the new task, use: Set T = oMSP.ActiveProject.Tasks.Add (Name:= value)

    where the Name argument comes from row R column 1 of Sheet1.

    You can use ActiveSheet in the macro instead of Sheets("Sheet1"). If you use ActiveSheet, then some worksheet (not necessarily Sheet1) must be active before you run the macro. The advantage of using notation like Sheets("Sheet1") is that Sheet1 doesn't have to be active - you can run the macro from the Excel module editor.

    Test run the macro once when MSProject is open and once when it's not. You should see the new project and tasks being added.

  4. Create a VB3 program that displays the task ids and names in a list box (use the tool). All the code is in the Form_Load procedure, except for a Close button that just has the End command. The list is initialized in the Form_Load procedure. Use an object variable oMSP to refer to MSProject and an object variable oTasks to refer to all the tasks in the active project. For n=1 to oTasks.Count, oTasks(n) represents a single task, so if oTasks(n) is not blank, then add an item to the list box where the item is the concatenation of the task Id and name separated by a space. In general, a string can be added to a list box List1 as follows:


    5. List1.AddItem string

    Note: To check if a task oTasks(n) is not blank, use the If/EndIf block that starts with:

    If Not oTasks(n) Is Nothing Then

    Open or create a test project with some nonblank tasks and some blank tasks, and run the VB3 program. Only the nonblank tasks should show in the list.

    The following limitations are from the VB3 Programmer's Guide, Appendix D: List boxes can

    hold 5440 items, each item has a maximum of 1K characters, and the total of all characters in the list box cannot exceed 64K.

  5. Create a VB3 program that, for the selected task, lists the names of all the resources assigned to that task in a list box. Form1 will have a list box and a Close button. Don't do any error checking. To test it, switch to MSProject and select a task that has some resources assigned to it; then switch back to VB3 and run the program. You should see the resource names assigned to the selected task show up in the VB3 listbox on Form1.


  6. Write an MSProject macro that starts a new invisible instance of Excel and creates a new workbook. For each nonblank task in the active project, the task names and durations are written to the first two columns of Sheet1 in the new workbook. Then the Excel Save As dialog comes up so you can enter a filename to save the new workbook. After that Excel quits. The only part of Excel you see is the Save As dialog and any overwrite warning. You don't get the "Save changes?" message. Write the macro so that the durations show up as text in the workbook just like they look in the task table. No blank rows are written to the worksheet. Test run the macro on a project that has several tasks including some blank rows, then open up the workbook it saved and see if it has the correct data.


  7. Write an MSProject macro that switches to Excel if it's running, otherwise starts a new visible instance and switches to it. The macro maximizes the Excel window and adds a new workbook. The task names and durations in the active project are written to the first two columns of Sheet1. The durations are written as pure numbers representing working days relative to ActiveProject.HoursPerDay. Then a chart sheet is added and the ChartWizard method is used to specify the source range for the chart to be the data in Sheet1. Assuming that x refers to Excel.Application, and that s refers to x.Sheets("Sheet1"), then the chart can be added with the following lines in the MSProject macro:


    8. x.Charts.Add

    x.ActiveChart.ChartWizard _

    Source:=s.Cells(1, 1).CurrentRegion, _

    PlotBy:=2, _

    CategoryLabels:=1