none
Exporting from MS Project 2010 to MS Excel - exporting blank date fields gives rogue dates RRS feed

  • Question

  • Hi all,

    I have a question regarding the exporting of dates using VBA. I have inserted the snippet of code that relates to the conversion process (I have not included the initialization of some key variables at the top as I dont believe it is needed to answer my question).

    The problem I am having is that when I use the code below I get rogue dates in the date cells in excel when the relevant fields are blank in MS project. In other words, whenever I have an empty cell in MS Project for the Start & Finish fields, when I use the code below I get rogue dates in the excel fields. I don't think these dates are rogue however I expect the Start and Finish properties of the Task object to return a blank/null if the field is empty in MS project. I have searched the Microsoft reference on these properties but haven't found anything that would help me understand this bug. Also, what's weird is, I tried using MS Project's "easy" method of transferring to excel using the wizard and creating a mapping, and using this approach I did indeed get blank cells when the fields were empty. 

    Any tips on this topic would be greatly appreciated. Thanks in advance.

    For Each rTask In ActiveSelection.Tasks

    With xlRange .Range("A3") = rTask.Text1 level = CInt(rTask.OutlineLevel) If level <> 1 Then taskId = rTask.Text1 startDate = rTask.Start finishDate = rTask.Finish .Range("B3") = rTask.Text4 tempNotes = Replace(Trim(rTask.Notes), vbCr, vbLf) tempNotes = Replace(tempNotes, vbVerticalTab, vbLf) .Range("C3") = tempNotes .Range("D3") = DateValue(rTask.Start) .Range("E3") = DateValue(rTask.Finish) .Range("F3") = rTask.ResourceNames .Range("G3") = rTask.Text6 .Range("H3") = rTask.Text10 .Range("I3") = rTask.Text5 .Range("J3") = rTask.Text7 End If End With Set xlRange = xlRange.Offset(1, 0)

    Next mtTask


    • Edited by yayo84 Wednesday, May 29, 2013 6:43 PM better clarity of code
    Wednesday, May 29, 2013 6:42 PM

Answers

  • yayo84,

    You mention blank cells for Start and Finish fields in Project, therefore you must be using manual scheduling. In manual scheduling mode, those fields are treated as text by Project instead of date fields as is the case when using auto scheduling. I'm guessing that's where the problem lies. So, counter to what you said about data type initialization not being relevant, the data type is indeed relevant.

    I'm not sure how Excel converts a blank text field to a date but when I used either the CDate or DateValue functions on a blank Start field via the immediate window in Project VBA, it gives the current date, in one case with the time included and in the other just the date.

    You can play around with data type conversions but one simple trick you might try is to detect a null text string (i.e. "") on the Project data and then treat it accordingly in Excel.

    Hope this helps.

    John

    Update:

    Upon looking into this further I found some interesting things about the supposedly "blank" text based Start, Finish and Duration fields when using manual scheduling mode. Although the display may show nothing in those field cells, there is in fact data in those fields when you look at Project's underlying database (i.e. Start, Finish and Duration properties of the task object). And the date data in the database depends on what may or may not be in those fields at the summary line. For example, let's say the user puts a date into the Start field of a manually scheduled summary line. Even though the Start fields for all subtasks under that summary may show as blank, they actually contain the date that was entered for the summary line. If there is no date at summary level, then all tasks, including the summary line will contain the Project Start date. The date will be text type and not date type but nonetheless there is a date, it's not blank as the screen display leads one to believe. No attempt to use "IsDate", DateValue", GetField, or other methods, including testing for a null text string as I suggest above, will yield the desired "blank" field cell when exporting to Excel.

    There are a couple solutions to the basic problem described in the original post. One is a bit "off-the-wall" but it will work. The second is a bit more robust.

    First method - If no date is entered into the Start or Finish fields of a manually scheduled task, Project will automatically set the Duration field to "1 day?", meaning an estimated duration. This value will not appear in the Duration field but it does exist when looking at the Duration property of the task object. Using this information, it is possible to use an InStr function on the Duration field to look for the "?" and thus determine if the Start and Finish fields are blank. This information can be incorporated into the export macro.

    Second method - Set up two custom text fields with the simple formulas, Text1=[Start] and Text2=[Finish]. If there is an entered date in the Start and/or Finish fields then the custom text fields will display that. However, if no date was entered into the Start and/or Finish fields then the custom text fields will be blank. The solution then is to export the contents of the custom text fields to Excel.

    If anyone has a better or more direct method for achieving what the original post described, then I'd love to hear it.

    • Marked as answer by yayo84 Thursday, May 30, 2013 3:56 PM
    • Edited by John - Project Thursday, May 30, 2013 6:06 PM further analysis
    Wednesday, May 29, 2013 8:02 PM

All replies

  • yayo84,

    You mention blank cells for Start and Finish fields in Project, therefore you must be using manual scheduling. In manual scheduling mode, those fields are treated as text by Project instead of date fields as is the case when using auto scheduling. I'm guessing that's where the problem lies. So, counter to what you said about data type initialization not being relevant, the data type is indeed relevant.

    I'm not sure how Excel converts a blank text field to a date but when I used either the CDate or DateValue functions on a blank Start field via the immediate window in Project VBA, it gives the current date, in one case with the time included and in the other just the date.

    You can play around with data type conversions but one simple trick you might try is to detect a null text string (i.e. "") on the Project data and then treat it accordingly in Excel.

    Hope this helps.

    John

    Update:

    Upon looking into this further I found some interesting things about the supposedly "blank" text based Start, Finish and Duration fields when using manual scheduling mode. Although the display may show nothing in those field cells, there is in fact data in those fields when you look at Project's underlying database (i.e. Start, Finish and Duration properties of the task object). And the date data in the database depends on what may or may not be in those fields at the summary line. For example, let's say the user puts a date into the Start field of a manually scheduled summary line. Even though the Start fields for all subtasks under that summary may show as blank, they actually contain the date that was entered for the summary line. If there is no date at summary level, then all tasks, including the summary line will contain the Project Start date. The date will be text type and not date type but nonetheless there is a date, it's not blank as the screen display leads one to believe. No attempt to use "IsDate", DateValue", GetField, or other methods, including testing for a null text string as I suggest above, will yield the desired "blank" field cell when exporting to Excel.

    There are a couple solutions to the basic problem described in the original post. One is a bit "off-the-wall" but it will work. The second is a bit more robust.

    First method - If no date is entered into the Start or Finish fields of a manually scheduled task, Project will automatically set the Duration field to "1 day?", meaning an estimated duration. This value will not appear in the Duration field but it does exist when looking at the Duration property of the task object. Using this information, it is possible to use an InStr function on the Duration field to look for the "?" and thus determine if the Start and Finish fields are blank. This information can be incorporated into the export macro.

    Second method - Set up two custom text fields with the simple formulas, Text1=[Start] and Text2=[Finish]. If there is an entered date in the Start and/or Finish fields then the custom text fields will display that. However, if no date was entered into the Start and/or Finish fields then the custom text fields will be blank. The solution then is to export the contents of the custom text fields to Excel.

    If anyone has a better or more direct method for achieving what the original post described, then I'd love to hear it.

    • Marked as answer by yayo84 Thursday, May 30, 2013 3:56 PM
    • Edited by John - Project Thursday, May 30, 2013 6:06 PM further analysis
    Wednesday, May 29, 2013 8:02 PM
  • John's right about manual tasks. You could try:

    If isdate(rTask.Start) then
        .Range("D3") = DateValue(rTask.Start)
    End If

    If isdate(rTask.Start) then
        .Range("E3") = DateValue(rTask.Finish)
    End If


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 29, 2013 8:22 PM
    Moderator