1. Macros for the Program Manager How to automate moving data from MS Project and MS Excel

2. Why ?

3. Agenda: Macros for the PM • What is a Macro? • The Power of the Macro • Things to Watch Out For • Write Your First Macro in Project • MS Project Global file • Sample Macros • Macro: Reading MS Project from Excel • Macro: Reading MS Excel from Project • Other Resources

4. What is a Macro? • A Macro is an action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language. A macro is a series of commands that are stored in a Visual Basic for Applications module. (For MS Project and Excel.)  • MS Project Macros are stored in modules in the Global.mpt file. • MS Excel Macros are stored as a part of the worksheet.

5. What is VBA? • VBA stands for Visual Basic for Applications. • VBA is similar to the programming language Visual Basic, but it is not the same. • VBA is used to write and edit Macros for Project, Excel, Word and even PowerPoint. • To open, on the Tools menu, point to Macros, and then click Visual Basic Editor. a powerful tool that allows you to work with your macros.

6. The VBA Interface

7. The Power of the Macro • Macros allow you to automate tasks that you perform on a regular basis, to save time • Supplement the analysis tools • Move data from MS Excel to MS Project • Move data from MS Project to MS Excel

8. Things to be Careful of • Security  Because macros can contain viruses, be careful about running them. Take the following precautions: • run up-to-date anti-virus software • set your macro security level to high • clear the Trust all installed add-ins and templates check box • use digital signatures & maintain a list of trusted publishers. • Remember to write and test all Marcos using a backed up version of your plan, as one bug can impact your entire plan

9. Write your first Project Macro • Plan the steps and commands you want the macro to perform. • Use the “Record Macro” functionality • After recording a macro, you use the Visual Basic Editor to edit and debug

10. Record New Macro menu snapshot

11. MS Project Global file • The global file allows you to copy macros between projects.

12. MS Project Global file Organizer snapshot

13. Sample Macros • Read MS Project from Excel to move a % complete to a XLS. • Create reports like “What’s due this week?” • Trace Utility – shows all predecessors and / or successors (http://masamiki.com/project/macros.htm)

14. Project Macro • Sample code for looping thru all task in a project plan to set Flag6 = False : Dim jTask As Task For Each jTask In ActiveProject.Tasks If Not (jTask Is Nothing) Then jTask.Flag6 = False End If Next jTask

15. Read Project data from Excelpart 1 A B C D E • Steps: • Clear out the data in cells C4 to E6 • Read the Directory information from Cell B1 • Read the Project file name from A4 • Read the Task name from B4 • Open the MS Project and the Project file (from A4) • Map the % complete from Project to Cell • Close MS Project

16. Referencing Project from Excel • Excel needs to see the “MS Project 9 Object Library” using Tools -> References in VBA

17. Read Project data from Excelpart 2 Sub Read_Perc() ' This Macro reads the % complete from a specific task name, in a specific project ' and updates the appropriate cell in the Excel worksheet based on values in cells. Dim Temp As Long Dim DirName, ProjectName As String ' Clean out any data from previous iteration and set time stamp. CleanSheet ' This step calls this sub routine. ' Find the Directory information in cell B1 and place it in field called DirName Range("B1").Select DirName = ActiveCell.FormulaR1C1 ' THIS NEEDS TO END WITH "/" ' Read first project name Range("A4").Select ' Find the Project name in cell A3. ProjectName = DirName & ActiveCell.FormulaR1C1 1. 2. 3.

18. Read Project data from Excelpart 3 Set MSProj = New MSProject.Application MSProject.FileOpen (ProjectName) Range("B4").Select ' Read the task name in cell B4. Temp = MSProject.Find("Name", "equals", ActiveCell.FormulaR1C1) Range("C4").Select ActiveCell.FormulaR1C1 = MSProject.ActiveCell.Task.PercentComplete / 100 Range("D4").Select ActiveCell.FormulaR1C1 = MSProject.ActiveCell.Task.ID Range("E4").Select ActiveCell.FormulaR1C1 = ActiveProject.Tasks.Count MSProject.FileClose ' Open the next project and repeat ... … MSProject.Quit End Sub 4. 5. 6. 7.

19. Read Excel Data from Projectpart 1

20. Read Excel Data from Projectpart 2 • Steps for reading data from Excel cells and putting it in to • an MS Project plan: • In VBA, add the reference to MS Excel 9.0 Object Library (or higher) • Define variables with Dim statements • Get the Project Object variable • Determine the Path information from the Project • Get the XLS file name from the Name of the Parent’s Child • Open the XLS spreadsheet • Loop thru the Tasks (Rows 3 – 5) using FileT.OutlineChildren • Use Excel’s VLookUp to find the % complete • Map the % complete to the appropriate task’s % complete • Close MS Excel

21. Read Excel Data from Project part 3 Sub Update_Percent() ' Macro Update_Percent ' This macro reads an MS Excel files, and updates the ' percent complete in the appropriate task in MS Project. ' ' 1. Done via the VBA Interface ' For this macro to see the MS Excel library of objects, add the reference to the ' MS Excel 9.0 Object Library - From the menu bar, select Tools then References. ' 2. Define the variables Dim xlApp As Excel.Application Dim FilesParent, ProjectTasks As Tasks Dim FileT, ProjectTaskT As Task Dim Proj As MSProject.Application Dim SpreadsheetName, XLSNameWithPath, TaskName As String Dim TempPercent As Integer ' 3. Get the Project Oject Variable Set Proj = GetObject(, "MSProject.Application") ' 4. Determine the path for the Project and Excel files. Set FilesParent = Proj.Application.ActiveProject.Tasks ' This is the name of the directory with the Excel. Need to add the "\" XLSPathName = FilesParent.Parent.Path & "\"

22. Read Excel Data from Project part 4 ' 5. Get the XLS file name from the Name of the first task's Child Set FileT = FilesParent.Item(1).OutlineChildren.Item(1) Set FilesT = FilesParent.Item(1).OutlineChildren.Item(1).OutlineChildren SpreadsheetName = FilesParent.Item(1).OutlineChildren.Item(1).Name XLSNameWithPath = XLSPathName & SpreadsheetName ' ' 6. Open the Excel Spreadsheet here Set xlApp = New Excel.Application xlApp.Visible = True xlApp.Workbooks.Open FileName:=XLSNameWithPath ' ' 7. Loop thru the Tasks (Rows 3 – 5) using FileT.OutlineChildren Set ProjectTasks = FileT.OutlineChildren For Each ProjectTaskT In ProjectTasks ' 7a. Use Excel’s VLookUp to find the % complete TaskName = ProjectTaskT.Name TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction.VLookup _ (TaskName,Sheets("Sheet1").Range("A4:D6"), 4, False) * 100 ' 7b. Map the % complete to the appropriate task’s % complete ProjectTaskT.PercentComplete = TempPercent Next ProjectTaskT

23. Read Excel Data from Project part 5 ' 8. Close MS Excel xlApp.Visible = False xlApp.Workbooks.Close xlApp.Quit Set xlApp = Nothing End Sub

24. Macro Tips and Tricks - General • Debugging: • Msgbox command to debug • Ctrl-Break is your friend! • Watch Lists • Proj.CalculateProject To perform a manual recalculation in MS Project • Adding an icon to a tool bar • Continue on the next line use “_” • Comments start with ‘

25. Other Resources • Google Groups • microsoft.public.excel.programming • www.ExcelForum.com/ • www.GanttHead.com • masamiki.com/project/macros.htm • www.mvps.org/project/ • Contact me!

26. Any Questions

27. Thank You! John Murphy Method 360 www.Method360.com JMurphy@Method360.com