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.
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