dbnet.home Bookmark and Share

MEET FEATURE REQUIREMENTS, SCHEDULE, and BUDGET! Consulting available to teach your organization to apply this methodology and tools for powerful quantitative management of your business processes.

  • Instruction of your staff at your site, using courseware, application example, and a functional template.
  • Mentoring your key staff on an ongoing or periodic basis, on these tools or on Quantitative Program Management.
  • Contracting or Employment in your organization on specific topics.

Brochure  ...  Contact for Details

Check out my YouTube Channel: Power Operational Intelligence

Now Live! Overview, Structure, Task Data, Table Design, SQL Server, and Re-Linking now showing.

Video courses covering material on this website, and more, are presented in the playlists.

Code snippet links at YouTube Code Snippets. Twitter at @poweroperation1, #poweropi, #poweroperationalintelligence.

Subscribe on YouTube, and click the "Notification" Bell icon to be notified as content is published.

ProjectLinks.gif

Integrating Microsoft Project with Excel and Visio

Microsoft Project is widely used by Program Managers to represent a project execution plan and to quantify a schedule. Project can output dates that can be integrated into a Build Plan to tie together dates with quantities and configurations, or into a Budget plan with date-dependent records describing expenditures, suppliers, build-items and so forth.

The first step is to output date data from MS Project. This can be done from the Project menu, and here is code to automate the operation. Use the menu Save-As ribbon item, and save the exported data as an Excel file. When doing so, specify at least the following fields for export: Task Description; Start Date; Finish Date and Task Unique Identifier. MSProject provides a unique identifier for each task, different from the task number. The task number changes as tasks are added or deleted, but the Unique Identifier does not change once the task is created. This allows other applications to reference the Unique Identifier in the exported data to find a specific task or milestone, for example specifying a delivery date. Note that if a project is inserted into another project as a sub-project (see Linking Projects below), all the tasks of the inserted sub-project are assigned Unique Identifiers that are unique to the rollup project.

Data from the file exported from Project can be used in an Excel workbook. The content of the exported file can be imported into a tab in the Excel workbook (typically automated in VB). Excel records describing build entities or budget entries would add a field indicating the Unique Identifier of the tasks providing a date for the record. Specification of "finish" or "start" can be made by each record as well to specify which date to use. Then a custom Vlookup function in an additional field in each record can find the Unique Identifer value specified in the data imported to the new tab. Here is code to automate the operation, using the Unique Identifier and "finish/start" indicator to extract the desired date. The import VB code described above sets up a named range for the imported data, and also named offset values relative to the Unique Identifier column for date columns, based on imported column names. When the field in each Excel record executes the custom Vlookup, the appropriate date is returned and can then be used when the record participates in a Pivot Table, Pivot Chart or other analysis.

Visio can be used to visualize a Microsoft Project plan using the file exported from Project described above. The version of Visio needed for this must support Data Linking. Currently, that is Visio Professional, or Visio Pro for Office365. Visio provides capability to link to external data, so the exported file can be specified as a source for that. Then Visio drawing objects can be linked to specific records in the linked data. In a particular example, when the data is refreshed VB code can be run to provide size and position of bars relative to a Timeline object to illustrate time sequencing of tasks and milestones. Here is code to perform this operation. Some code is also provided to drop a .pdf and a .gif image of the Visio figure.

The design described here is based on exporting and importing an Excel file, which I've found the easiest to implement. But instead of exporting an Excel file and then importing that file into Excel and linking UIDs via VLookup, the exported Excel file could be linked as a table into Access or another database along with the other tables from Excel, and linked via UID in a SQL query. It's also possible to export from Project to XML, and import the XML into a database. When the exported XML file is imported into a database, the Tasks table imported contains the task dates and UID, which can again be linked to records from tables linked from Excel that specify a UID to indicate tasks containing needed date information from your Project plan. This import is a little more complicated (but maybe is a more elegant design!) and the import will include several other tables that need to be managed, perhaps deleted if their information is not useful in this instance. But there is plenty of information in Project that your application may need, so this is how you get it into Excel.

SystemTop1175

Linking Projects in Microsoft Project

A complex program may be broken into multiple sub-programs to manage complexity, each represented by a project file, and also to allow multiple people to update files representing the part of the program for which they are responsible.

Ultimately, these sub-programs must be linked to represent the full project schedule. Microsoft Project links tasks between programs by referring to the link number of linked-to task. This presents a problem: any time a task is added to a project file, link numbers of all tasks below it are updated, breaking any links to those tasks unless all linked sub-project files are opened concurrently in the same MSProject client.

To avoid this, a header section can be added to each project file for "Links In" and "Links Out". Milestone tasks are added into these sections, linking to tasks within the same project file. It is the milestones in these header sections that will be referenced among linked project files, instead of the tasks below describing the actual project work tasks. Then as tasks are added or deleted in the project below the "Links In" and "Links Out" sections, the dates of the milestones in the header section are updated but each of the header milestones retains its task number so links remain intact. This allows sub-program managers to update their files independently without breaking links. This works for all task updates, except for the linked tasks in the header section. Before addressing that, let's cover a method to represent the whole program, that facilitates linking among sub-programs.

The sub-program project files can be inserted as sub-programs into a Rollup-Project file describing the full program. This makes it convenient to create links among the header milestones in the sub-programs since the Rollup-Project opens all of the sub-programs. MSProject updates links properly when project files involved in a link are simultaneously open in an MSProject client. So to add new linkages among sub-programs, the Rollup-Project is opened, and new header milestones can be added and linked. When the Rollup-Project is saved, also save the sub-programs when MSProject asks for each one; then all the links including new ones added will retain proper linking. After this save, local sub-program managers can continue to open and modify their sub-project files independently, excluding changes to these header milestones which must use the procedure in this paragraph. Also note that MSProject cannot link in this way among mixed 2007 and 2010 files, so save all files in a common format.

Finally, task export with the Unique Identifier described above is best handled from the Rollup-Project. The Rollup-Project contains all the tasks from the sub-programs project files, each with a Unique Identifier which is unique to the Rollup-Project and different from the Unique Identifier assigned in each separate sub-project file. Code provided in the Rollup-Project can export all these tasks, for use as described above in build plans, fab plans, budgets and so forth.