Share via

MS Project Troubleshooting: Truncated Project Notes when Viewing/Exporting

Viewing Project’s Notes field

Background

Project’s Notes field is the only text based field that will hold and display more than 255 characters of text. If the text string is 255 characters or less and does not have any line feeds or carriage returns, a user can simply add the Notes field to any task view. However, if the information in the Notes field is greater than 255 characters or has line feeds/carriage returns the user must either view the Notes tab of the Task Information window or find another way to view the information. Project does provide one other way to view the full notes text for each task and that is through File/Print/Page Setup/View tab. Select the option to Print notes.

However, users may find it more convenient to export Project’s data to another application for customized viewing and reporting. The other application of choice is Excel. Unfortunately a copy and paste or export using the export wizard under File/Save As/Save as type: Excel workbook, will not copy or export the full Notes field text. The notes text will be truncated at 255 characters or the first line feed/carriage return, whichever occurs first.

Export-to-View Options

So what is the best way to export and view Project’s full Notes field text, along with other Project fields? A save to XML and then to Excel will probably work, but that is an extra step. A preferred method is to export Project data, including the full notes text, to Excel using VBA. A VBA procedure can take different approaches.

  1. Direct transfer - read a line of data from Project, write it immediately into Excel. Repeat this process for all tasks
  2. Indirect transfer - read Project data into a text file (e.g. Text, CSV) and then open that text file in Excel.
  3. Bulk transfer - read all Project data into a set of arrays. Then open Excel and dump the arrays.

The direct transfer approach is fairly simple to implement but it does slow down the process since execution continually jumps between Project and Excel. The indirect transfer method avoids the process slowdown of the direct transfer method but it does require an extra step (i.e. separate text file). The bulk transfer method is slightly more complex to implement but it runs the fastest and provides immediate results in the form of a pre-formatted Excel Workbook.

One other item needs to be mentioned. Although a user can format the text in the Notes field (i.e. Font characteristics, alignment, bullets, and inserted objects), none of these characteristics are visible in the raw ASCII text which is stored internally. Therefore, none of these characteristics will be captured for export by any of the means mentioned above.

As a side note, users are reminded that there are actually three separate Notes fields in Project, each one associated with Project’s three basic object types - Task, Resource and Assignment. For more information see: http://social.technet.microsoft.com/wiki/contents/articles/31991.ms-project-data-types-task-resource-assignment.aspx

A companion article on importing Excel data into Project is addressed in a separate Wiki article (Ref:xyz)

VBA to Export Project Notes and Other Selected Fields to Excel

Macro Overview

The VBA procedure below was written several years ago, (circa Project 2000), and updated as necessary to fix issues and be more compatible with new versions of Project. The current version is 1.4, updated on 9/4/14 for this article. In it’s basic form, the macro exports the following Project fields into a pre-formatted Excel Workbook. Fields can be easily added or deleted by the user as desired.

  • Task ID
  • Task Name
  • Resource Names
  • Scheduled Start
  • Scheduled Finish
  • Task Notes

If the Project file is filtered, only data from the filtered set of tasks will be exported.

The macro uses the bulk transfer method noted above so for those new to VBA, it provides an example of how to use arrays. For most files, the whole process runs very fast with a message at the end telling the user the export is complete. The code also provides progress messages in the Windows caption bar but those will likely only be seen if the Project file is very large with lots of data to export.

Included with the export macro code are two utilities users may find useful. The first checks and displays all current object library references that are set on the user’s system. The second finds and removes all line feeds that may be present in the Notes field of the active Project file.

Object Library References

Since this macro operates on two separate applications, Project and Excel, it is important to insure all object library references are set prior to running the macro, (early binding),  or a runtime error will occur. Once the references are set for the user’s PC, they will not have to be set again for subsequent runs of the macro.

The first screenshot below shows how to get to the Set References window. The second screenshot shows the minimum set of references for this macro. (Note, the reference for the Forms object library is not required for this macro, it appears because it is needed by other macros on the author’s system). Both screenshots are typical for Project 2010 and Office 2010. The user should ensure the correct library versions are selected for their application version (e.g. 16.0 for Project 2013 and Office 2013).

Installation and Setup

The following steps are suggested for installing and running the macro.

  1.  Copy the entire code below so that it will be in your Windows scratchpad, then Open Project

  2. If using Project 2010 or later select the Developer tab on the ribbon and double click the “Visual Basic” icon.

    If using Project 2007 or earlier, go to Tools/Macro/Visual Basic Editor

  3.  In the VB Editor window, go to Tools/References as shown above and set or check references as needed.

  4. Then go to Insert/Module and paste. That will copy the macro code from your scratchpad to the VB Editor window

  5. If using Project 2010 or later go to File/Save Global.MPT. That will make the macro available to run on any Project file

     If using Project 2007 or earlier, go to File/Save [current open project]. That will save the macro to the current open file. It is suggested the user use the Organizer to transfer the module to the Global file. If the module is not transferred to the user’s Global, the current file must be opened any time the user wishes to run the macro.

  6. If not already open, open the file from which you wish to export data.

  7. If using Project 2010 or later, go to Developer/Code group/View Macros

     If using Project 2007 or earlier, go to Tools/Macro/Macros

  8. Select the macro “Export_Notes_Text_NBL” and hit “Run”

Precaution

The copy and paste process can sometimes experience a glitch that, when working with macro code, may introduce artifacts into the code that are not visible when viewed in the VB Editor. However, the VB compiler will detect these artifacts and flag an error without any usable help in figuring out why. If this happens, the best approach is to delete the module created in step 4 above, then repeat the installation and setup steps.

Export Macro Code

'Macro written by John - Project
'Version 1.5 7/24/18 11:00 am
'   updates & fixes (oldest to most current)
'   *added declaration for index variables
'   *included separate procedure for checking object library references
'   *changed array dimension statements for active selection so procedure works with consolidated files
'   *changed all constant designations for line feed and carriage return
'   *added declaration for remaining undeclared variables
'   *changed code to recognize and handle vertical tabs
'   *fixed problem with writing to caption that occurs with some Windows installations
'   *changed export to scheduled start/finish instead of baseline start/finish
'   *added Resource Names field to export and version number as variable
'   *added statement to reset "on error goto" after Excel is called
'   *added format for date value in Excel to only show date without the time
'   *removed license agreement for public release
'   *added statement to remove horizontal tabs from Notes string
Option Explicit
Option Compare Text
Public Const  ver = " - 1.5"
Sub Export_Notes_Text_NBL()
Dim TskID() As Integer
Dim TskNam() As String
Dim ResNam() As String
Dim SStart() As Date
Dim SFinish() As Date
Dim TskNot() As String
Dim NumTsk As Integer, i As  Integer, j As Integer, RowIndex As  Integer
Dim BookNam As String
Dim t As Task
Dim Xl As Excel.Application
Dim s As Worksheet
Dim c As Range
'set array sizes based on number of tasks in file
SelectTaskColumn
NumTsk = ActiveSelection.Tasks.Count
ReDim TskID(NumTsk), TskNam(NumTsk), ResNam(NumTsk), SStart(NumTsk), SFinish(NumTsk)
ReDim TskNot(NumTsk)
MsgBox "This macro exports the following Project fields to Excel:" & vbCr & _
    "   Task ID" & vbCr & "   Task Name" & vbCr & _
    "   Resource Names" & vbCr & _
    "   Scheduled Start" & vbCr & "   Scheduled Finish" & vbCr & _
    "   Task Notes" & vbCr & vbCr & _
    "Note: only data for tasks in the current view will be exported", _
    vbInformation, "Export to Excel"  & ver
'First, gather desired data from Project in arrays
  
Application.Caption = "Progress"
ActiveWindow.Caption = " Gathering Project data into arrays"
i = 1
For Each  t In  ActiveSelection.Tasks
    If Not  t Is  Nothing Then
        TskID(i) = t.ID
        TskNam(i) = t.Name
        ResNam(i) = t.ResourceNames
        SStart(i) = t.ScheduledStart
        SFinish(i) = t.ScheduledFinish
        TskNot(i) = Replace(Trim(t.Notes), vbCr, vbLf)
        TskNot(i) = Replace(TskNot(i), vbVerticalTab, vbLf)
        TskNot(i) = Replace(TskNot(i), vbTab, vbLf)
        i = i + 1
    End If
Next t
  
'Second, set up existing instance of Excel, or if Excel is not running, start it
On Error  Resume Next
Set Xl = GetObject(, "Excel.application")
If Err <> 0 Then
    On Error  GoTo 0
    Set Xl = CreateObject("Excel.Application")
    If Err <> 0 Then
        MsgBox "Excel application is not available on this workstation" _
            & vbCr & "Install Excel or check network connection", vbCritical, _
            "Notes Text Export - Fatal Error"
        FilterApply Name:="all tasks"
        Set Xl = Nothing
        On Error  GoTo 0     'clear error function
        Exit Sub
    End If
End If
On Error  GoTo 0
Xl.Workbooks.Add
BookNam = Xl.ActiveWorkbook.Name
       
'Keep Excel in the background and minimized until export is done (speeds transfer)
'NOTE: Items with a 'Reference annotation will not work without a reference to the Excel object library
Xl.Visible = False
Xl.ScreenUpdating = False
Xl.DisplayAlerts = False
ActiveWindow.Caption = " Writing data to worksheet"
'Third, dump arrays into the Workbook
Set s = Xl.Workbooks(BookNam).Worksheets(1)
ActiveWindow.Caption = " do it again"
s.Range("A1").Value = "ID"
s.Range("B1").Value = "Task Name"
s.Range("C1").Value = "Sched Start"
s.Range("D1").Value = "Sched Finish"
s.Range("E1").Value = "Res Names"
s.Range("F1").Value = "Notes"
Set c = s.Range("A2")
RowIndex = 0
For j = 1 To i - 1
    c.offset(RowIndex, 0).Value = TskID(j)
    c.offset(RowIndex, 1).Value = TskNam(j)
    c.offset(RowIndex, 2).Value = SStart(j)
    c.offset(RowIndex, 3).Value = SFinish(j)
    c.offset(RowIndex, 4).Value = ResNam(j)
    c.offset(RowIndex, 5).Value = TskNot(j)
    RowIndex = RowIndex + 1
Next j
'Fourth, format the Workbook
s.Rows(1).Font.Bold = True
s.Columns("A").AutoFit
s.Columns("C:D").AutoFit
s.Columns("C:D").NumberFormat = "m/d/yy;@"
s.Columns("B").columnwidth = 25
s.Columns("E").columnwidth = 25
s.Columns("F").columnwidth = 80
s.Range("B:B,E:F").WrapText = True
s.Columns("A:F").VerticalAlignment = xlTop 'reference
s.Range("C:D").HorizontalAlignment = xlLeft 'reference
'Finally, close and exit
MsgBox "Data Export is complete", vbOKOnly, "Notes Text Export"
Application.Caption = ""
ActiveWindow.Caption = ""
Xl.Visible = True
Xl.ScreenUpdating = True
Set Xl = Nothing
End Sub
'This utility will print out the current object library references to the Immediate Window.
Sub Chk_ObjLib_Refs()
Dim oRef As Object
For Each  oRef In  ThisProject.VBProject.References
    Debug.Print oRef.Description
    Debug.Print oRef.fullpath
Next
End Sub
'This utility will find and remove all line feeds that may be present in the Notes field
'   It will also report via the Immediate Window where it found the line feeds and how many
Sub remove_LFs()
Dim TstStr As String, NewStr As  String
Dim p1 As Integer, LFcntr As  Integer
Dim t As Task
For Each  t In  ActiveProject.Tasks
    If Not  t Is  Nothing Then
        If Len(t.Notes) > 0 Then
        Debug.Print "ID " & t.ID & " - " & Len(t.Notes) & " chars"
            NewStr = ""
            TstStr = t.Notes
            LFcntr = 0
            While InStr(1, TstStr, vbCr) > 0
                LFcntr = LFcntr + 1
                p1 = InStr(1, TstStr, vbCr)
                NewStr = NewStr & Mid(TstStr, 1, p1 - 1)
                TstStr = Mid(TstStr, p1 + 1)
            Wend
            t.Notes = NewStr & TstStr
            Debug.Print " found " & LFcntr & " line feeds"
            Debug.Print " ID now has "  & Len(t.Notes) & " chars"
        End If
    End If
Next t             
End Sub