Using Excel VBA to retrieve data from multiple MS Project Files

Question

I have ran into an automation issue that I cannot seem to figure out.

Currently, I have a worksheet,("Project") that contains data in columns "A"(Project Name) & "B"(Project File Location). Column "B" contains the string location of each MS Project file.

My VBA macro loops through column "B" and opens each MS Project file and copies a task with the .SelectTaskField method and then copies it back into column "E" of the worksheet.

The first 2 projects loop through without any issues, however, on the 3rd project, I receive the Run-time error '1004': An unexpected error occurred with the method. I co-worker and I have poured through the code and the MS Project Files to see if there are any differences in the data and we cannot find any differences.

Below is a copy of the code that I have been using. Just wanted to see if anyone else has had similar issues. I have found that MS Project does not like to be manipulated like Excel or Word.

Any help would be greatly appreciated.

Sub Test()
Dim ws As Worksheet
Set ws = Worksheets("Projects")
Dim lrow As Long
lrow = Range("B" & Rows.Count).End(xlUp).Row
'Turns off updates and alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Select Daily Field Reports and clear worksheet
ws.Range("E2:E" & lrow).ClearContents
'Opens MS Project
Set objproject = CreateObject("MSProject.Project")
'This keeps MS Project invisible. If you want to see it, change to "True"
objproject.Application.Visible = True
        Dim oproject As Range
        'This cycles through the range and gathers the data for each project
        For Each oproject In Range("B2:B" & lrow)
        Set objproject = CreateObject("MSProject.Project")
            oproject.Select
            objproject.Application.FileOpen Selection
            objproject.Application.Visible = True
            objproject.Application.SelectTaskField Row:=1, Column:="Percent Complete", RowRelative:=False  'The column name must match. This is the only issue that I have uncovered.
            objproject.Application.EditCopy
            ws.Select
            Dim lastrow As Long
            lastrow = ws.Cells(Rows.Count, "E").End(xlUp).Row + 1
            Dim Rng As Range
            Set Rng = ws.Range("E" & lastrow)
            'Rng.PasteSpecial xlPasteFormats
            Rng.PasteSpecial xlPasteValues
            objproject.Application.Quit
        Next oproject
'Turns updates and alerts back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Closes MS Project
objproject.Application.Quit

End Sub

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2020-05-14T16:35:21

Using the SelectTaskField method presumes the file was saved in a task view and that the column you want is in the table of the view. Better to get the values you need directly from the Task object.

It appears you are looking for the % Complete value from the first task. In that case use this:

objproject.ActiveProject.Tasks(1).PercentComplete

Here's how it could work in your code. I took the liberty of simplifying it a bit:

Sub Test()
    Dim ws As Worksheet
    Set ws = Worksheets("Projects")
    Dim lrow As Long
    lrow = Range("B" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    ws.Range("E2:E" & lrow).ClearContents
    Dim objproject As MSProject.Application
    Set objproject = CreateObject("MSProject.Application")
    objproject.Application.Visible = True
    Dim oproject As Range
    For Each oproject In Range("B2:B" & lrow)
        objproject.FileOpen Name:=oproject.Value, ReadOnly:=True
        oproject.Offset(, 3) = objproject.ActiveProject.Tasks(1).PercentComplete
        objproject.FileCloseEx
    Next oproject
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    objproject.Quit
End Sub

Note that it is more straight-forward to get a reference to the application object rather than a child of that object: CreateObject("MSProject.Application") is preferable to CreateObject("MSProject.Project").

Using Excel VBA to retrieve data from multiple MS Project Files

1 Answers