Macros for the program manager
Download
1 / 27

macros for the program manager - PowerPoint PPT Presentation


  • 266 Views
  • Updated On :

Macros for the Program Manager. How to automate moving data from MS Project and MS Excel. Why ?. 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

Related searches for macros for the program manager

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'macros for the program manager' - sandra_john


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Macros for the program manager l.jpg

Macros for the Program Manager

How to automate moving data from MS Project and MS Excel



Agenda macros for the pm l.jpg
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


What is a macro l.jpg
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.


What is vba l.jpg
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.



The power of the macro l.jpg
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


Things to be careful of l.jpg
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


Write your first project macro l.jpg
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



Ms project global file l.jpg
MS Project Global file

  • The global file allows you to copy macros between projects.



Sample macros l.jpg
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)


Project macro l.jpg
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


Read project data from excel part 1 l.jpg
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


Referencing project from excel l.jpg
Referencing Project from Excel

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


Read project data from excel part 2 l.jpg
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.


Read project data from excel part 3 l.jpg
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.


Read excel data from project part 1 l.jpg
Read Excel Data from Projectpart 1


Read excel data from project part 2 l.jpg
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


Read excel data from project part 3 l.jpg
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 & "\"


Read excel data from project part 4 l.jpg
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


Read excel data from project part 5 l.jpg
Read Excel Data from Project part 5

' 8. Close MS Excel

xlApp.Visible = False

xlApp.Workbooks.Close

xlApp.Quit

Set xlApp = Nothing

End Sub


Macro tips and tricks general l.jpg
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 ‘


Other resources l.jpg
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!



Thank you l.jpg

Thank You!

John Murphy

Method 360

www.Method360.com

[email protected]


ad