macros for the program manager l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Macros for the Program Manager PowerPoint Presentation
Download Presentation
Macros for the Program Manager

Loading in 2 Seconds...

play fullscreen
1 / 27

Macros for the Program Manager - PowerPoint PPT Presentation


  • 271 Views
  • Uploaded 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

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

Macros for the Program Manager

How to automate moving data from MS Project and MS Excel

agenda macros for the pm
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
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
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
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
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
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
MS Project Global file
  • The global file allows you to copy macros between projects.
sample macros
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
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
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
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
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
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 2
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
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
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
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
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
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

Thank You!

John Murphy

Method 360

www.Method360.com

JMurphy@Method360.com