Importing / Linking MS Project Dates into Excel - Recommendations or #REF! Error Resolution Help

Question

Software: MS Project Online Desktop Client MSO 16.0.13127.21210 32-bit use

I am trying to import MS Project start / finish dates from a MS project file that is on SharePoint into an excel file and keep the import linked between excel and MS project such that anytime the date changes in MS Project, my excel file dates update accordingly.

The first method I tried was selected and copying a date in the SharePoint MS Project File and doing a Paste Special - Paste Link - "As" text

This correctly inserted a date into my excel file using the following syntax:

=MSProject.Project.9|'<>\EUMDR PH-GT Therapy'!'!LINK_26'

The problem is as soon as I close the excel file and try to open it up again, all the dates change to #REF!. It does not seem like excel can keep the connection between the Excel Office 365 file and the SharePoint MS Project file. Is there any recommendations or settings on who to fix this. I completed a test in which I saved the MS Project File to my desktop and made the same Paste line command. This time the syntax looked like the following:

=MSProject.Project.9|'C:\Users\XXXXX\OneDrive - Company Name\Project Name\Product List\Timeline Summary\Archive\Timeline Summary 06-10-21.mpp'!'!LINK_23'

This time when I closed the excel file and re-opened it, it was able to re-establish the dates so it appears the paste link function is having an issue of maintaining a connection between excel and the SharePoint MS project file version. Any suggestions to fix the connection so I would not have to do yet another step to save an off-line version each time I want to insure no dates in my excel file as changed.

Alternatively, I have tried saving the MS Project file as a workbook and using the import wizard and map to export the data to excel. I figured if I could get that to work I could just have my excel file linked to the exported MS project workbook file. The problem with that is the dates are exported as text with a MM/DD/YYYY and TIME and once in excel there is not an easy way I have found to convert that test string into a date that excel recognized correctly.

Answer 1

Shooter71,

With paste links you will know things went south (i.e. corrupt) when data gets messed up, links don't work, you get error messages, or any number of other undesirable behaviors.

The macro in the Wiki article I suggested is one way to transfer data from Project to Excel. In that case, the macro does indeed create a new Excel file to dump the Project data. However, it is just as easy to export selected Project data to a specific Excel file or vice versa (i.e. Excel file to a specific Project file). Options are virtually endless.

The difference between option 2 and 3 is function and reliability. Option 2 is rolling the dice, option 3 (VBA) is winning the game.

You mention that your goal is to compare an Excel summary file to a Project file. That sounds a little different than what you original post noted. What exactly is the "compare" part? Do you have an existing Excel summary report that you simply want to update with Project data when there is a change in the Project schedule?

John

Answer 2

Hi John, thank you for the continued support. I think my goal has been the same from the first post to my last, but maybe I am not explaining it very well. I believe you correctly captured my goal with your last question in which I have an existing Excel summary report that I simply want to update with Project data when there is a change in the Project schedule.

So to summarize to be ultra clear of my specifics, I have a MS Project File that contains lets say 400 tasks. Of these 400 tasks, there are 50 milestone tasks (0 day duration with the same start / finish date) which I am tracking in an excel summary report for a task finish date. Anytime one of the finish dates changes in my MS Project File, I want that date in my excel file to update.

Now I am sharing this detail as the macro currently just pulls all 400 tasks and dumps them into an new excel file. Once I open the new excel file exported by project, I then somehow need to link the data back to my excel summary file.

You mentioned a couple of options I would have to figure out first before I could make Option 3 really work in which I am not linking my Excel Summary file to the excel file exported out of MS Project for the 50 tasks I am interested in:

1. Understand how to program the macro to export data not to a new generic BOOK1.XLS file, but point the export directly into my Excel Summary excel file or at least a new sheet within the Excel Summary file.
2. Link the 50 specific tasks I am interested out of the 400 tasks that are on the MS project file. The 50 milestone tasks are mixed in throughout the MS Project file, so I cannot program say just export the first 50 task for example.

Do you have any links that can help me figure out these two steps that I would believe are needed to be successful, or any other suggestions on how to accomplish this goal since it appears you are aligned with my overall goal?

Answer 3

Shooter71,

I'd be happy to write the code to do what you want. It would be easier than a back and forth to help you make the mods to the existing code. I've done this many times with other users. Contact me at the address below, I will ask some questions. One thing I will need is the path to the Excel file summary report and if possible, it would be helpful to have your Project file and Excel file.

John

jmacprojataticlouddotdotcom

(remove obvious redundancies)

Answer 4

John - Information sent; please advise if any issue receiving it along with the explanation I submitted in the email body.

Answer 5

Hello John,

I emailed a week or so ago some follow-up questions for the macro you originally suggested. The macro works as designed but I was looking to expand it to include the baseline start and baseline finish dates. My edits are not working and was hoping you or someone else could help define where I errored in adding these dates to the export file.

'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 BStart() As Date
Dim BFinish() 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), BStart(NumTsk), BFinish(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 & _
    "   Baseline Start" & vbCr & "   Baseline 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
        BStart(i) = t.BaselineStart
        BFinish(i) = t.BaselineFinish
        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 = "Baseline Start"
s.Range("F1").Value = "Baseline Finish"
s.Range("G1").Value = "Res Names"
s.Range("H1").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 = BStart(j)
    c.Offset(RowIndex, 5).Value = BFinish(j)
    c.Offset(RowIndex, 6).Value = ResNam(j)
    c.Offset(RowIndex, 7).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:F").NumberFormat = "m/d/yy;@"
s.Columns("B").ColumnWidth = 25
s.Columns("E").ColumnWidth = 25
s.Columns("F").ColumnWidth = 25
s.Columns("G").ColumnWidth = 25
s.Columns("H").ColumnWidth = 80
s.Range("B:B,E:H").WrapText = True
s.Columns("A:H").VerticalAlignment = xlTop 'reference
s.Range("C:F").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