1
votes

Greets to all.

I'm facing this problema for two months ago without solution.

I need to get baseline start and finish dates from MS Project file into Excel by VBA, a piece of algorith that works is like this:

Set Prj = MSProject.Application
Dim tarea As Task

For Each tarea In Prj.ActiveProject.Tasks


Select Case BL

Case "BL0"
    StartD = CDate(Format(tarea.BaselineStart, "dd/mm/yyyy"))
    EndD   = CDate(Format(tarea.BaselineFinish, "dd/mm/yyyy"))

Case "BL1"
    StartD = CDate(Format(tarea.Baseline1Start, "dd/mm/yyyy"))
    EndD   = CDate(Format(tarea.Baseline1Finish, "dd/mm/yyyy"))

Case "BL2"
    StartD = CDate(Format(tarea.Baseline2Start, "dd/mm/yyyy"))
    EndD   = CDate(Format(tarea.Baseline2Finish, "dd/mm/yyyy"))

Case "BL3"
    StartD = CDate(Format(tarea.Baseline3Start, "dd/mm/yyyy"))
    EndD   = CDate(Format(tarea.Baseline3Finish, "dd/mm/yyyy"))
...

... and so until BL10; but this code is extremly inefficient and takes much more time to import dates when Project file has more than two thousand task lines.

I tried solution I found in next link to write just one line of code for different baselines https://social.msdn.microsoft.com/Forums/en-US/c964e627-079e-4296-9e5c-b7c82900d982/project-2010-standard-vba-export-to-excel-latest-baseline?forum=project2010custprog, but there is a little difference in results,

StartD = tarea.Baseline3Start --> StartD = 11/06/2014 07:00:00 a.m.

StartD = tarea.GetField(Application.FieldNameToFieldConstant("Baseline" & "3" & "Start", pjTask)) --> StartD = "wdn 11/06/14"

With second instruction vba does not get the exactly baseline date, it gets the field value as it is showed in MS Project and I can not convert this last result in a valid date value into Excel

I have also tried with Eval() and Evaluate() functions to write a general code to get this dates

BLstring = "tarea.Baseline" & "3" & "Start"
Eval(BLstring) --> Error 2409

It is understandable because this command is not a native Excel function

Is there any way I can get these dates without to use Select case sentence? Or, may be, Is there a way to execute a string as MS Project outer command to get those dates?

Any idea will be much help for me.

EDIT #0: Finally, after two months of slowly and painful suffering, I have found out a solution.

Andrew Eversight Solution's does not work for me because it takes same method to get value from MSProject... It looks for PjFiel enumeration value (http://msdn.microsoft.com/en-us/library/ff867782(v=office.15).aspx) which it is 188744184, to get field task value:

StartD = tarea.GetField(Application.FieldNameToFieldConstant("Baseline" & "3" & "Start", pjTask)) --> StartD = "wdn 11/06/14"

It is equivalent to

StartD = Tsk.GetField(tsk_fld_BaselineStart) --> StartD = "wdn 11/06/14"

For both case I get field value by pjFiel number

FieldNameToFieldConstant("Baseline" & "3" & "Start", pjTask) = 188744184
tsk_fld_BaselineStart = pjTaskBaseline3Start = 188744184

tarea.GetField instructions does the rest and result is the same ( "wdn 11/06/14" )

As Rachel Hettinger said, GetField method always returns a string and, for some reason I don't look to understand, CDate does not convert this result into a valid date value.

Change application default date format, as Rachel Heetinger says, it is not reliable at this point, I don't want to modify any Project file (at this moment).

I can get correct value by using CallByName:

BLstring = "Baseline" & "3" & "Start"

For Each tarea In Prj.ActiveProject.Tasks

    StartD = CallByName(tarea, BLstring, VbGet)
    StartD_aux = tarea.Baseline3Start

...

Both instructions give same result ( 11/06/2014 07:00:00 a.m. )

Dear Andrew and Rachel thank you for your fast answer and help on time.

1
FYI: When reading date values, it is helpful to change the Application.DefaultDateFormat to a format easily converted to a date (e.g. pjDate_mm_dd_yyyy). Just be sure to change it back to whatever the user had.Rachel Hettinger

1 Answers

1
votes

Performance is never going to be great when you've got a couple of thousand lines, but I can show you what I do, which is kind of a combination of the two.

First, you use the select case statement to get the name of the field you want to use before you start looping through the tasks:

Dim tsk_fld_BaselineStart As PjField
Dim tsk_fld_BaselineFinish As PjField

Select Case str_BaselineDestFieldSet

    Case "Baseline"
        tsk_fld_BaselineStart = pjTaskBaselineStart
        tsk_fld_BaselineFinish = pjTaskBaselineFinish
    Case "Baseline1"
        tsk_fld_BaselineStart = pjTaskBaseline1Start
        tsk_fld_BaselineFinish = pjTaskBaseline1Finish
    Case "Baseline2"
        tsk_fld_BaselineStart = pjTaskBaseline2Start
        tsk_fld_BaselineFinish = pjTaskBaseline2Finish

'... and so on

End Select

Having got these fields captured in variables, when you loop through the tasks you don't have to perform that enormous select case statement, or convert a string to a field value, as it's already done:

Dim Tsk As Task
Dim StartD As Date
Dim EndD As Date

For Each Tsk In ActiveProject.Tasks

    StartD = CDate(Tsk.GetField(tsk_fld_BaselineStart))
    EndD = CDate(Tsk.GetField(tsk_fld_BaselineFinish))

    Debug.Print "Task """ & Tsk.Name & """ starts " & StartD & " and ends " & EndD

Next Tsk

Make sure you explicitly declare StartD and EndD as date variables, as that may be why you're getting the wrong format back. That's not a problem I've experienced before. UPDATE 11/11: Rachel has clarified this point in the comments - code above has been updated to include CDate function on the dates.

If this is going to be regularly used on large projects, it may be worth implementing a progress bar to show the user that it is progressing during execution, as even with this performance enhancement, it could take some time to run.