Referencing MSProject in Excel VBA produces Runtim

Question

My objective is to loop through a number of MS Project documents where the file paths (e.g. L:\Project\Scchedule.mpp) are stored on worksheet "Projects" in column C (beginning in cell C2).

This line returns runtime error 91 (object variable or with block variable not set). PrjApp.Application.FileOpenEx PrjFullName

Sub OpenProjectCopyPasteData()

Dim PrjApp      As MSProject.Application
Dim aProg       As MSProject.Project
Dim PrjRange    As Range
Dim PrjFullName As String
Dim t           As Task
Dim rng         As Range
Dim rng1        As Range
Dim rng2        As Range
Dim rng3        As Range
Dim ws1         As Worksheet
Dim ws2         As Worksheet
Dim MyCell      As Range

Set ws1 = Worksheets("Project Data")
Set rng1 = ws1.Range("A:D")
Set rng2 = ws1.Range("F:F")
Set ws2 = Worksheets("Projects")
Set PrjRange = ws2.Range("C2")
Set PrjRange = Range(PrjRange, PrjRange.End(xlDown))


'Clear current contents of Project Data tab
rng1.ClearContents
rng2.ClearContents

For Each MyCell In PrjRange

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Open MS Project file
PrjFullName = MyCell
If PrjFullName = "" Then GoTo 99

PrjApp.Application.FileOpenEx PrjFullName
Set aProg = PrjApp.ActiveProject

' show all tasks
OutlineShowAllTasks

'Copy the project columns and paste into Excel
SelectTaskColumn Column:="Name"
EditCopy
Set ws1 = Worksheets("Project Data")
Set rng = ws1.Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
rng.PasteSpecial xlPasteValues
rng.PasteSpecial xlPasteFormats

SelectTaskColumn Column:="Resource Names"
EditCopy
Set rng = ws1.Range("B" & Cells(Rows.Count, "B").End(xlUp).Row + 1)
rng.PasteSpecial xlPasteValues
rng.PasteSpecial xlPasteFormats

SelectTaskColumn Column:="Finish"
EditCopy
Set rng = ws1.Range("F" & Cells(Rows.Count, "F").End(xlUp).Row + 1)
rng.PasteSpecial xlPasteValues
rng.PasteSpecial xlPasteFormats

SelectTaskColumn Column:="Text1"
EditCopy
Set rng = ws1.Range("C" & Cells(Rows.Count, "C").End(xlUp).Row + 1)
rng.PasteSpecial xlPasteValues
rng.PasteSpecial xlPasteFormats

SelectTaskColumn Column:="Text2"
EditCopy
Set rng = ws1.Range("D" & Cells(Rows.Count, "D").End(xlUp).Row + 1)
rng.PasteSpecial xlPasteValues
rng.PasteSpecial xlPasteFormats

' reset settings of Excel and MS-Project
Application.DisplayAlerts = True
Application.ScreenUpdating = True
PrjApp.ScreenUpdating = True
PrjApp.DisplayAlerts = True

'PrjApp.FileClose False
PrjApp.Quit pjDoNotSave
Set PrjApp = Nothing

Next MyCell

99 Sheets("Projects").Activate

End Sub

Answer 1

So turns out the issue was I was somehow shutting down the project application before the loop command. Needed to move it after the loop command. Specifically, these lines:

 PrjApp.FileClose False
 PrjApp.Quit pjDoNotSave
 Set PrjApp = Nothing

Thanks to everyone for your time and suggestions!

Answer 2

When you call

PrjApp.Application.FileOpenEx PrjFullName

PrjApp is Nothing, which is causing the error. Prior to this call, add a line

set PrjApp = new MSProject.Application