none
Project 2010 Standard - VBA export to Excel; Latest Baseline RRS feed

  • Question

  • I am using Project 2010 Standard.  I have written a VBA module to export certain fields of my Projects to Excel.  I use the same Module for multiple project files.  Two of the fields I export are baseline starts and baseline finishes for each task in a Project file.  The problem I have is that each of my projects are at different baselines, so I need to go into my VBA code and change which baseline start and finish I want to export.  Is there a way to write the code to look for the latest baseline set for each project file, and export the start and finishes stored with the latest baseline?  My first approach was to write the code so an input box opens asking the user to input the correct baseline number, but I don't know how to substitute the Task.BaselineStart field with the user specified baseline number (i.e. Baseline2Start).

    Below is my Module:

    Sub InchstoneExport()

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Set xlApp = New Excel.Application
        xlApp.Visible = True
        AppActivate "Microsoft Excel"
        Set xlBook = xlApp.Workbooks.Open("G:\Users\Gilbert_Shawn\MS_Project\InchstoneCount_working.xlsm")
        Set xlSheet = xlBook.Worksheets("RawData")
           
        For Each Task In ActiveProject.Tasks
            xlSheet.cells(Task.ID + 2, 1).Value = Task.ID
            xlSheet.cells(Task.ID + 2, 2).Value = Task.Name
            xlSheet.cells(Task.ID + 2, 3).Value = Task.BaselineStart
            xlSheet.cells(Task.ID + 2, 4).Value = Task.BaselineFinish
            xlSheet.cells(Task.ID + 2, 5).Value = Task.Start
            xlSheet.cells(Task.ID + 2, 6).Value = Task.Finish
            xlSheet.cells(Task.ID + 2, 7).Value = Task.ActualStart
            xlSheet.cells(Task.ID + 2, 8).Value = Task.ActualFinish
            xlSheet.cells(Task.ID + 2, 9).Value = Task.Summary
            xlSheet.cells(Task.ID + 2, 10).Value = Task.Recurring
            xlSheet.cells(Task.ID + 2, 11).Value = Task.Flag1
        Next Task

    End Sub

    Tuesday, May 14, 2013 2:12 PM

Answers

  • Thanks Rod.  I like this simple approach.  I'm very new to VBA, so please bear with me....  Using the same idea, can I do the following:

        Dim strBaseline As String
        strBaseline = InputBox("Input Baseline. (i.e. Baseline1)")
        Debug.Print ActiveProject.Tasks.GetField(Application.FieldNameToFieldConstant( _
                strBaseline & "Start", pjTask))

    Assuming this method works, once this is done, how do I then tie this back into my module above to export the correct baseline values for each task in my loop?

    Shawn

    Monday, June 3, 2013 3:15 PM
  • Shawn,

    Rod's probably counting sheep right now so let me get you set up.

    To keep things simple, I would create a couple of date variables (i.e Dim BS as Date, BF as Date):

    BS=Task.GetField(Application.FieldNameToFieldConstant(strBaseline & "Start",pjTask))

    BF=Task.GetField(Application.FieldNameToFieldConstand(strBaseline & "Finish",pjTask))

    Insert those as the first statements in the For Each loop and then use BS and BF as the Value variables instead of:

    Task.BaselineStart

    Task.BaselineFinish

    Hope this helps.

    John

    Monday, June 3, 2013 3:36 PM

All replies

  • Shawn,

    There are a couple ways you can approach this, an automated approach or a user specified approach. You've touched on both in your problem description.

    For the automated approach you mention the "latest baseline". I'm not sure if you mean the latest as in the highest numbered baseline or you mean by actual date the baseline was set. The latter information for the normal baseline can be read using:

    activeproject.baselinesaveddate(pjbaseline)

    You could then loop through each baseline and see which date is the latest. Baselines that are not set will return "NA".

    For a user specified approach you will need to create a userform. In that userform set up a variable for the selected baseline and then use that variable as the Value in your existing macro.

    The automated approach will be less complicated (i.e. no userform needed), but a userform can expand your options if you think you may want to make your macro more flexible in the future (i.e. other user defined operations). If you do decide to go the userform route, make sure you install the following hotfix:

    http://support.microsoft.com/kb/2596585?wa=wsignin1.0

    John

    Tuesday, May 14, 2013 3:27 PM
  • I try to keep things as simple as possible. I would enter Baseline, Baseline1 or Baseline2 etc into a Text field for the Project Summary Task. This tells you what Baseline to use. Then:

    Sub Test()
    Dim strBaseline As String
        strBaseline = ActiveProject.ProjectSummaryTask.Text1
        Debug.Print ActiveProject.Tasks(1).GetField(Application.FieldNameToFieldConstant( _
    strBaseline & "Start", pjTask)) End Sub
    The code shows the required value in the Immediate window. Now the same code works in any project.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Tuesday, May 14, 2013 8:31 PM
    Moderator
  • If this is just a routine you run yourself, then it probably makes sense to use John/rod's simplified approach.

    However, I've done something similar the long winded way before. I had a user form with a dropdown with the baseiln, basline1, basline2, etc. values in it. The user selected one of these value then clicked "Export" which executed the sub routine. The sub routine then resolved this string value to a set of pj fields, as shown below:

    Select Case str_BaselineSelection Case "Baseline" tsk_fld_BaselineStart = pjTaskBaselineStart tsk_fld_BaselineFinish = pjTaskBaselineFinish tsk_fld_BaselineDuration = pjTaskBaselineDurationText tsk_fld_BaselineDurationEstimated = pjTaskBaselineDurationEstimated tsk_fld_BaselineWork = pjTaskBaselineWork tsk_fld_BaselineCost = pjTaskBaselineCost tsk_TimescaledBaselineWork = pjTaskTimescaledBaselineWork ass_TimescaledBaselineWork = pjAssignmentTimescaledBaselineWork Case "Baseline 1" tsk_fld_BaselineStart = pjTaskBaseline1Start tsk_fld_BaselineFinish = pjTaskBaseline1Finish tsk_fld_BaselineDuration = pjTaskBaseline1DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline1DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline1Work tsk_fld_BaselineCost = pjTaskBaseline1Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline1Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline1Work Case "Baseline 2" tsk_fld_BaselineStart = pjTaskBaseline2Start tsk_fld_BaselineFinish = pjTaskBaseline2Finish tsk_fld_BaselineDuration = pjTaskBaseline2DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline2DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline2Work tsk_fld_BaselineCost = pjTaskBaseline2Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline2Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline2Work Case "Baseline 3" tsk_fld_BaselineStart = pjTaskBaseline3Start tsk_fld_BaselineFinish = pjTaskBaseline3Finish tsk_fld_BaselineDuration = pjTaskBaseline3DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline3DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline3Work tsk_fld_BaselineCost = pjTaskBaseline3Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline3Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline3Work Case "Baseline 4" tsk_fld_BaselineStart = pjTaskBaseline4Start tsk_fld_BaselineFinish = pjTaskBaseline4Finish tsk_fld_BaselineDuration = pjTaskBaseline4DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline4DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline4Work tsk_fld_BaselineCost = pjTaskBaseline4Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline4Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline4Work Case "Baseline 5" tsk_fld_BaselineStart = pjTaskBaseline5Start tsk_fld_BaselineFinish = pjTaskBaseline5Finish tsk_fld_BaselineDuration = pjTaskBaseline5DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline5DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline5Work tsk_fld_BaselineCost = pjTaskBaseline5Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline5Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline5Work Case "Baseline 6" tsk_fld_BaselineStart = pjTaskBaseline6Start tsk_fld_BaselineFinish = pjTaskBaseline6Finish tsk_fld_BaselineDuration = pjTaskBaseline6DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline6DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline6Work tsk_fld_BaselineCost = pjTaskBaseline6Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline6Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline6Work Case "Baseline 7" tsk_fld_BaselineStart = pjTaskBaseline7Start tsk_fld_BaselineFinish = pjTaskBaseline7Finish tsk_fld_BaselineDuration = pjTaskBaseline7DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline7DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline7Work tsk_fld_BaselineCost = pjTaskBaseline7Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline7Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline7Work Case "Baseline 8" tsk_fld_BaselineStart = pjTaskBaseline8Start tsk_fld_BaselineFinish = pjTaskBaseline8Finish tsk_fld_BaselineDuration = pjTaskBaseline8DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline8DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline8Work tsk_fld_BaselineCost = pjTaskBaseline8Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline8Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline8Work Case "Baseline 9" tsk_fld_BaselineStart = pjTaskBaseline9Start tsk_fld_BaselineFinish = pjTaskBaseline9Finish tsk_fld_BaselineDuration = pjTaskBaseline9DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline9DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline9Work tsk_fld_BaselineCost = pjTaskBaseline9Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline9Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline9Work Case "Baseline 10" tsk_fld_BaselineStart = pjTaskBaseline10Start tsk_fld_BaselineFinish = pjTaskBaseline10Finish tsk_fld_BaselineDuration = pjTaskBaseline10DurationText tsk_fld_BaselineDurationEstimated = pjTaskBaseline10DurationEstimated tsk_fld_BaselineWork = pjTaskBaseline10Work tsk_fld_BaselineCost = pjTaskBaseline10Cost tsk_TimescaledBaselineWork = pjTaskTimescaledBaseline10Work ass_TimescaledBaselineWork = pjAssignmentTimescaledBaseline10Work

    End Select

    Then, when you wanted to call the variable for a specific task you could use the following:

        For Each Task In ActiveProject.Tasks
            xlSheet.cells(Task.ID + 2, 1).Value = Task.ID
            xlSheet.cells(Task.ID + 2, 2).Value = Task.Name
            xlSheet.cells(Task.ID + 2, 3).Value = Task.getfield(tsk_fld_BaselineStart)
            xlSheet.cells(Task.ID + 2, 4).Value = Task.getfield(tsk_fld_BaselineFinish)
            xlSheet.cells(Task.ID + 2, 5).Value = Task.Start
            xlSheet.cells(Task.ID + 2, 6).Value = Task.Finish
            xlSheet.cells(Task.ID + 2, 7).Value = Task.ActualStart
            xlSheet.cells(Task.ID + 2, 8).Value = Task.ActualFinish
            xlSheet.cells(Task.ID + 2, 9).Value = Task.Summary
            xlSheet.cells(Task.ID + 2, 10).Value = Task.Recurring
            xlSheet.cells(Task.ID + 2, 11).Value = Task.Flag1
        Next Task

    As Rod says, you could dispense with the form and use a text field on the project summary task instead. You could probably dispense with the select statement using the string concatenation method for passing the field names Rod has given as well.

    Like I said, this is the long winded way, but thought it would be useful to share my experience with the same problem.

    Hope this helps,
    Andrew


    Thursday, May 16, 2013 8:08 AM
  • Thanks Rod.  I like this simple approach.  I'm very new to VBA, so please bear with me....  Using the same idea, can I do the following:

        Dim strBaseline As String
        strBaseline = InputBox("Input Baseline. (i.e. Baseline1)")
        Debug.Print ActiveProject.Tasks.GetField(Application.FieldNameToFieldConstant( _
                strBaseline & "Start", pjTask))

    Assuming this method works, once this is done, how do I then tie this back into my module above to export the correct baseline values for each task in my loop?

    Shawn

    Monday, June 3, 2013 3:15 PM
  • Shawn,

    Rod's probably counting sheep right now so let me get you set up.

    To keep things simple, I would create a couple of date variables (i.e Dim BS as Date, BF as Date):

    BS=Task.GetField(Application.FieldNameToFieldConstant(strBaseline & "Start",pjTask))

    BF=Task.GetField(Application.FieldNameToFieldConstand(strBaseline & "Finish",pjTask))

    Insert those as the first statements in the For Each loop and then use BS and BF as the Value variables instead of:

    Task.BaselineStart

    Task.BaselineFinish

    Hope this helps.

    John

    Monday, June 3, 2013 3:36 PM
  • Perfect.  Thanks John (and Rod).

    It works great now.

    Shawn

    Monday, June 3, 2013 6:23 PM
  • Shawn,

    You're welcome and thanks for the feedback.

    John

    Monday, June 3, 2013 7:09 PM