MS Project Data copy into Excel spreadsheet with VBA

[WarsEagle]

Hello everyone,

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