PJ204Presented by Johnny JohnsonJohnny Johnson
Mr. Johnson is a technical integration consultant for Micro-Frame Technologies, Inc. He has taught computer science at the U.S. Naval Academy and business management courses at community colleges. He has developed large scale project management and database systems for the FAA, Air Force, Martin Marietta, as well as other government and commercial customers. He is a Microsoft Project Solutions Partner and Trainer.
Phone: (206) 598-5842
Fax: (206) 598-5843
CompuServe: 74415,1237
Internet: silvrsof@interserv.com
Microsoft Project 4.1 can be used to create powerful applications because of the many development tools that are included. Tools such as a Macro Recorder, VBA (Visual Basic for Applications) language, OLE, and customizable views, forms, menus, and toolbars. I will demonstrate each of these functions and how to maximize Microsoft Project functionality.
The following application uses Microsoft Project's customizable views, toolbars, reports, and VBA to create a Moving Company Estimation Tool. It can produce a tabular resource cost and a Cost loaded Gantt chart. The Visual Basic routine calculates and displays the cost of each task on the Gantt chart. The model is based on contracted Packing and Delivery dates. In addition it calculates the storage costs for a shipment by creating a variable length task that is equal to the free slack between loading goods into storage and when they have to be removed for delivery.
The finished Gantt view is shown in the picture below:
Moving Company Gantt View
The Visual Basic macro was assigned to a button on the TechEd 95 toolbar, which launches the calculations and updates the Gantt chart. The calculations are based upon the resource sheet labor rates and the values entered into the Moving Company Entry table.
Moving Company Entry Table
The Moving Company Bar Text Macro requires two loops through the model. The first pass sets the variable StorageDuration equal to the free slack, then sets the Storage Cost task duration equal to it. The second pass accumulates cost and formats the bar text.
'Macro MovingCompanyBarText 'Macro Created by Johnny Johnson. Sub MovingCompanyBarText() Dim t, R As Object Dim TotalMileage, TotalCost, StorageDuration As Variant TotalMileage = 0 ' Calculate Storage duration as a function of Free Slack For Each t In ActiveProject.Tasks If t.Name = "Unload Goods Into Storage" Then StorageDuration = t.FreeSlack End If If t.Name = "Storage Costs" Then t.Duration = StorageDuration End If Next t For Each t In ActiveProject.Tasks t.Cost1 = 0 TotalMileage = TotalMileage + t.Number1 t.Number2 = TotalMileage ' Accumulate costs for each task and total cost For Each R In t.Assignments TotalCost = TotalCost + R.Cost t.Cost1 = t.Cost1 + R.Cost Next R t.Text3 = "$ " & Str(TotalCost) 'Format Start and Stop dates in same text field If t.Number1 > 0 Then t.Text4 = Str(t.Number1) & "mi - " & t.Text1 & " - " & t.Text2 Else If t.Milestone = True Then t.Text4 = Str(TotalMileage) & " Total Miles - " & t.Text3 & " Total Cost" Else t.Text4 = "" End If End If Next t End Sub
The custom Moving Cost Estimate Report is a weekly resource cost report.
Moving Company Cost Estimate
The macro that follows was written entirely in Microsoft Project 4.1's VBA but references Microsoft Excel 7.0's VBA. When building cross-application macros, it is always best to reference the other applications type libraries. To reference another instance VBA you must be in the Macro Edit mode. Go to the Tools pull-down menu, then select "References". Click on the appropriate library.
Location of VBA Reference Dialog
As you change your GLOBAL.MPT or load different .MPT files you need to check to ensure that the correct references and paths are correct or your macro may not work and you could get an OLE automation error.
VBA Reference Dialog
An excellent way to get really customized tabular reports is to send tabular data to Microsoft Excel 5.0 via OLE automation, then automate the process using Microsoft Project and Microsoft Excel to create powerful reports.
Simple Report Generator Scheme
VBA Code for Project 4.0 sending Resource Assignment information by Task to Excel where it is formatted with a "Pivot Table" for analysis and reporting.
'This Macro gets all of the resources assigned by task and sends 'them to Microsoft Excel 5.0 for formatting into a pivot table. 'NOTE: Ensure that the XLEN50.OLB is referenced in Microsoft Project... While in edit macro mode, go to TOOLS Menu, then select References, then 'check "Microsoft Excel 5.0 Object Library" (XLEN50.OLB) Sub JJProj2xl5pivot() Const pjTaskDuration = 29 'Duration Const pjTaskFinish = 36 'Finish Const pjTaskName = 14 'Name Const pjTaskStart = 35 'Start Const ROWSTART = 1 Const COLSTART = 1 Dim xl As Object Dim R%, C% Dim Tsk, ResAssignment Dim TaskFields Dim FieldNames Dim I% 'array of constants for GetField method TaskFields = Array(pjTaskName,pjTaskDuration,pjTaskStart, pjTaskFinish, -1) 'Column headings FieldNames = Array("Task Name","Task Duration","Task Start", "Task Finish","Resource ID","Resource Name", "Units","Work","Actual Work","Remaining Work","") AppExecute "Microsoft Excel", "EXCEL.EXE" Set xl = GetObject(class:="Excel.Application") xl.Workbooks.Add 'For each task in current project copy fields over to excel R = ROWSTART C = COLSTART I = 0 'add column headings While FieldNames(I) <> "" xl.Cells(R, C + I).value = FieldNames(I) I = I + 1 Wend For Each Tsk In ActiveProject.Tasks For Each ResAssignment In Tsk.Assignments C = COLSTART R = R + 1 'send current task info I = 0 While TaskFields(I) <> -1 xl.Cells(R,C).value= Tsk.GetField(FieldID:=TaskFields(I)) C = C + 1 I = I + 1 Wend 'send resource assignment info xl.Cells(R, C).value = ResAssignment.ResourceID C = C + 1 xl.Cells(R,C).value = ResAssignment.ResourceName C = C + 1 xl.Cells(R,C).value = ResAssignment.Units C = C + 1 xl.Cells(R,C).value = (ResAssignment.Work/60) C = C + 1 xl.Cells(R,C).value =(ResAssignment.ActualWork/60) C = C + 1 xl.Cells(R,C).value=(ResAssignment.RemainingWork/60) Next ResAssignment Next Tsk 'Create and format Excel 5.0 pivot table With xl (application object) 'Select Range of data for pivot table .Range(.Cells(ROWSTART, COLSTART),.Cells(R,C)).Select 'Create pivot table using Excel pivot table wizard .ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=.Range(.Cells(ROWSTART,COLSTART), .Cells(R, C)), TableDestination:="R30C1", TableName:="PivotTableRes" 'Add Row Fields to pivot table .ActiveSheet.PivotTables("PivotTableRes").AddFields RowFields:=Array("Task Name", "Resource Name") 'Add data field .ActiveSheet.PivotTables("PivotTableRes"). PivotFields("Work").Orientation = xlDataField 'Select completed pivot table and autoformat .ActiveSheet.PivotTables("PivotTableRes"). TableRange1.Select .Selection.AutoFormat Format:=xlList1, Number:=True, Font:=True,Alignment:=True, Border:=True, Pattern:=True,Width:=True .Selection.EntireColumn.AutoFit End With End Sub
Resulting Excel 7.0 Pivot Table
One of the hardest things to do in Microsoft Project 4.0 is to graph resource load by day and by task. Although you can see, using the resource usage view, the amount of hours assigned to a resource each day, you cannot easily see the breakdown per task. The following VBA macro, prompts the user for a span of dates and the resource name to analyze. This macro also takes a different approach in that it collects the desired data in Microsoft Project 4.1 and creates a custom formatted "CSV" (comma separated value) export file. This is to increase the speed for Excel to load and format the resulting graph. In my experience there are times when this is much faster than OLE automation, especially if you are moving large volumes of data.
Entering Start Date for Resource Spread
Entering Ending Date for Resource Spread
Entering desired resource ( could have entered Car)
Carpenter Resource Load/Day
The key VBA function that makes this resource assignment spread possible is the TimeScaledData function. This function returns a string like "2h,1h,0h,4h,8h,1h" and the trick to sending this to Excel is to remove the units and leave 2,1,0,4,8,1 so that Excel can process the hours as numbers. In addition to hours, you can get this data in any time units and as work or cost.
TimeScaledData Function Call Reference 'temp = TimeScaledData(t.ID, r.ID, ActiveProject.ProjStart, _ ActiveProject.ProjectFinish, pjWork, pjTimescaleWeeks) 'Macro JJAssignmentsOverTime 'Macro Created 12/12/95 8:45 AM by Johnny Johnson. Sub JJAssignmentsOverTime() Dim t, R, f As Object Dim Temp, ResSpread, WhichResource As String Dim strpos, TaskCount, ElapsedDays As Integer Dim StartDate, EndDate As Variant Dim Message, Title, Default, Msg, style Dim TransferFile On Error GoTo OtherError TransferFile = "respread.csv" ' Set prompt for Start Date. Message = "Enter Starting Date (Default to CurrentDate)" Title = "Input Start Date" ' Set title. Default = ActiveProject.CurrentDate ' Set default. ' Display message, title, and default value. StartDate = InputBox(Message, Title, Default) 'Validate StartDate for null value If StartDate = "" Then GoTo DataError: ' Set prompt for Finish Date. Message = "Enter Finish Date (Default to Project Finish _ Date)" Title = "Input Finish Date" ' Set title. Default = ActiveProject.ProjectFinish ' Set default. ' Display message, title, and default value. EndDate = InputBox(Message, Title, Default) 'Validate EndDate for null value If EndDate = "" Then GoTo DataError: WhichResource = InputBox(Prompt:="Enter Resource to _ Display...", Title:="Determine Daily Resource _ Spread", Default:="*") ViewApply Name:="Gantt Chart", singlePane:=True If WhichResource <> "*" Then FilterApply Name:="Select Resource", _ Value1:=WhichResource End If SelectAll Open TransferFile For Output As #1 'Print First three header lines for Excel... Print #1, ActiveProject.Name & "," & ActiveProject.ProjectStart _ & "," & ActiveProject.ProjectFinish Print #1, "Resource Spread Dates Start: " & StartDate & _ " Finish: " & EndDate Print #1, "TaskID," & "ResID," & "Resource Name," & "Task _ Name," & StartDate TaskCount = 0 'For Each t In ActiveProject.Tasks For Each t In ActiveSelection.Tasks For Each R In t.Resources If InStr(R.Name, WhichResource) <> 0 _ Or WhichResource = "*" Then Temp = TimescaledData(t.ID, R.ID, StartDate, _ EndDate, pjWork, pjTimescaleDays) 'This loop takes the "h" out of the string so that 'Excel can treat it like a number strpos = 1 While strpos <= Len(Temp) If Mid(Temp, strpos, 1) <> "h" Then ResSpread = ResSpread & Mid(Temp, strpos, 1) End If strpos = strpos + 1 Wend 'Print the data line to the output file Print #1, t.ID & "," & R.ID & "," & R.Name & "," _ & t.Name & "," & ResSpread 'Reset the ResSrpead string variable to "" ResSpread = "" TaskCount = TaskCount + 1 End If Next R Next t Close #1 'Ensure there is something to look at... If TaskCount = 0 Then MsgBox "There was no Resource Selected" Else 'Start Excel in the background Set xl = CreateObject(class:="Excel.Sheet.5") With xl.Application ' Makes XL visible (if left out cannot close XL) .Visible = True .Workbooks.OpenText FileName:=TransferFile, _ DataType:=xlDelimited, comma:=True .ActiveCell.Offset(2, 0).Rows("1:1").EntireRow.Select .Selection.NumberFormat = "m/d/yy" .Range("E3").Select .Selection.AutoFill Destination:=.Range(.cells(3, 5), _ .cells(3, ElapsedDays + 5)), Type:=xlFillDefault .Range(.cells(3, 5), .cells(3, ElapsedDays + 5)).Select .ActiveCell.Offset(1, 0).Rows("1:" & _ TaskCount).EntireRow.Select .Selection.Sort Key1:=.Range("B4"),Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom 'Create Chart .ActiveCell.Offset(-1, 2).Range(.cells(1, 1), _ .cells(TaskCount + 1, ElapsedDays + 5)).Select .ActiveSheet.ChartObjects.Add(484.5, 4.5, 423, _ 219).Select .Application.CutCopyMode = True .ActiveChart.ChartWizard Source:=.Range(.cells(3, 3), _ .cells(TaskCount + 1, ElapsedDays + 5)), _ Gallery:=xlColumn, _ Format:=3, PlotBy:=xlRows, _ CategoryLabels:=1, _ SeriesLabels:=2, HasLegend:=1, _ Title:="Resource Spread for " & WhichResource, _ CategoryTitle:="Date", ValueTitle:="Hours/Day", _ ExtraTitle:="" .ActiveChart.Visible End With xl = Nothing End If GoTo EndError: DataError: Msg = "You didn't enter a valid date, Macro halted." style = vbOKOnly + vbExclamation ' Define buttons. Title = "Date Validation Warning" ' Define title. ' Display message. Response = MsgBox(Msg, style, Title) OtherError: Close #1 EndError: End Sub
Many times you don't want to clutter your project management schedule with lots of detailed short time tasks, milestones or to-do lists but it would be nice to reference these items to a related task in your schedule. That's the purpose of this mini-application, to maintain a checklist of items in Microsoft Excel that relate to a schedule. The way I designed this, was to have an Excel workbook related to a project and each "tabbed" work sheet corresponds to a project phase, which is identified by the Microsoft Project Text9 field. Flag10 is used to indicate a task that has a corresponding checklist in the Microsoft Excel workbook.
To create a custom dialog form, Select Tools, Customize, Forms to bring up the Custom Form Dialog. Select new and design the form by selecting the fields and text then positioning them on the form.
Custom Form Dialog
Dialog Editor
Finished form created in Microsoft Project to make updating user defined fields easier
In addition to creating a custom form to speed up data entry, you can attach a macro to a button on the toolbar and further customize your application. To add a blank button to a toolbar, hold the Shift key down and click on a blank space on the toolbar. After the blank button appears, holding the Control key down and clicking on the blank button will bring up the Customize Tool Dialog box. Select the function or Macro to assign to the button from the Command list box and enter a Tool Tip, if required. If you want to customize the picture or create a new on, select Edit Button and get creative.
Customize Tool Dialog
Button Editor Dialog
The first thing you have to do is to create a Workbook and then a Worksheet for each phase of your project. Insert worksheets by "Right-Clicking" on the worksheet tab, then select insert. To name the tabs to match the phase, "double-click" on the tab and enter the Phase name. After you have done that, you can bring in the tasks for each phase, by pasting them into the appropriate worksheet, then label columns B-F to the appropriate dates for updating. You could paste-link the dates in from your Microsoft Project schedule, but for the purposes of this demo, I will update the dates through VBA code. The finished checklist is shown and is called "checklist.xls" for purposes of the demo.
Checklist WorkBook
'Macro JJExcelCheckList Macro Created by Johnny Johnson. Sub JJExcelCheckList() Dim t As Object, xl As Object Dim ChkLst As Variant Dim TaskStart, TaskFinish As Date, Dim ActStart, ActFinish As Variant Dim CheckListFilename As String CheckListFilename = "c:\teched96\chklists.xls" If Not (ActiveSelection.Tasks Is Nothing) Then For Each t In ActiveSelection.Tasks If t.Flag10 = True Then ChkLst = t.Text9 TaskStart = t.Start TaskFinish = t.Finish ActStart = t.ActualStart ActFinish = t.ActualFinish SelectRowStart Set xl = CreateObject _ (class:="Excel.Application") xl.Workbooks.Open FileName:=CheckListFilename xl.Application.Visible = True 'Make XL visible (if left out cannot close XL) xl.Worksheets(ChkLst).Activate xl.Columns("A").Find(t.Name).Activate xl.WindowState = xlMaximized CurrentRow = xl.ActiveCell.Row CurrentColumn = xl.ActiveCell.Column xl.cells(CurrentRow, CurrentColumn + 1). _ Value = TaskStart xl.cells(CurrentRow, CurrentColumn + 2). _ Value = TaskFinish xl.cells(CurrentRow, CurrentColumn + 3). _ Value = ActStart xl.cells(CurrentRow, CurrentColumn + 4). _ Value = ActFinish Else SelectRowStart Message Message:="The currently selected task has no associated checklist." End If Next End If End Sub
PowerPoint 7.0 allows you to create presentations directly, using VBA and OLE. Suppose you want to create a quick summary presentation from a project. The following macro takes data from summary tasks and sends it to a formatted PowerPoint presentation as follows:
PowerPoint starts in Presentation Mode and displays several slides of summary data from Microsoft Project directly.
The last of three pages of data is an embedded object.
'Macro PowerPointDemoTE96 'Macro Created Mon 5/22/95, Modified 1/5/96. Global oPPT, oPres, oSlide, oPlace As Object Global oTask, oChildTask As Task Global xNum As Integer Global myStr, myResStr, strFileNamePath As String Global Const myTemplate = "C:\blue diagonal.pot" Sub PowerPointDemoTE96() Dim Msg, Title, style Msg = "This macro creates a PowerPoint presentation for the active project. The VBA procedure is called ProjPresentIt. You must set references to the PPT object library for this to run successfully. If you have not done these things, click cancel to quite the macro. Otherwise, click OK to continue. style = vbOKCancel + vbInformation 'Define the buttons Title = "Welcome" 'Define the title. Response = MsgBox(Msg, style, Title) 'Display message. If Response = vbOK Then ' User chose Yes Call ProjPresentIt End If End Sub Sub GetPowerPoint95() On Error GoTo Errorhandler Set oPPT = CreateObject("PowerPoint.Application.7") Set oPres = oPPT.Presentations.Add Exit Sub Errorhandler: RetVal = Shell("c:\apps\office95\powerpnt\powerpnt.exe", 1) SendKeys "{ESC}", True Set oPPT = CreateObject("PowerPoint.Application.7") oPPT.AppWindow.WindowState = ppWindowMixed Exit Sub End Sub Sub ProjPresentIt() Call GetPowerPoint95 'counter xNum = 1 'Summary data to PowerPoint For Each oTask In ActiveProject.OutlineChildren Set oSlide = oPres.Slides.Add(xNum, 2) oSlide.Objects.Title.Text = oTask.Name If Not (oTask.OutlineChildren Is Nothing) Then Set oPlace = oSlide.Objects.Placeholders(1) oPlace.Text = "Manager: " & oTask.ResourceNames Set paraRange = oPlace.Text.Paragraphs(1) paraRange.Append Chr(13) & "Status: " & oTask.PercentComplete & "% Complete" paraRange.Append Chr(13) & "Planned Cost: $" & Format$(oTask.BaselineCost) paraRange.Append Chr(13) & "Actual Cost: $" & Format$(oTask.ActualCost) paraRange.Append Chr(13) & "Start: " & Format$(oTask.Start, "m/d/yy") paraRange.Append Chr(13) & "Finish: " & Format$(oTask.Finish, "m/d/yy") For Each oChildTask In oTask.OutlineChildren myResStr = oChildTask.ResourceNames myStr = myResStr & ", " & myStr Next oChildTask paraRange.Append Chr(13) & "Team: " & Left$(myStr, Len(myStr) - 2) myStr = "" End If xNum = xNum + 1 Next oTask 'Create a title slide Set oSlide = oPres.Slides.Add(1, 1) oSlide.Objects.Title.Text = ActiveProject.Title Set oPlace = oSlide.Objects.Placeholders(1) oPlace.Text = "Manager: " & ActiveProject.Manager & Chr(13) & Format$(Now, "m/d/yy") 'get file location of the active file strFileNamePath = ActiveProject.FullName 'strFileNamePath = "c:\project1.mpp" 'embed a copy of the file onto the last slide With oPres.Slides.Add(oPres.Slides.Count + 1, ppLayoutObject) With .Objects .Title.Text = ActiveProject.Title .AddOleObjectFromFile strFileNamePath, boolLink, _ .Placeholders(1).Left, .Placeholders(1).Top, .Placeholders(1).Width, _ .Placeholders(1).Height End With End With 'display the slide show oPres.SlideShow.Run (ppSlideShowFullScreen) End Sub
As you can see, using the powerful tools included in Microsoft Project 4.1 you can easily integrate project management data into the Microsoft Office environment. As more products include VBA, the potential to make tightly bound applications will be even greater. All of the demos shown are found on the TechEd96 CD-ROM. The files included are: