Referencing MSProject in Excel VBA produces Runtime Error 91 object variable or with block variable not set

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

(a) A MSProject.Application object probably does not contain an Application method (but it might) but, more importantly ... (b) you have never set PrjApp to anything so it is still Nothing. Try setting it to a new application, i.e. Set PrjApp = New MSProject.Application. (I can't test it because I don't have Project.) — YowE3K
– YowE3K, Commented May 16, 2017 at 22:12
What are the options in setting PrjApp? I'm confused because it's an application. — ERKSMTY
– ERKSMTY, Commented May 16, 2017 at 22:16
I updated my comment (probably after you read it) - try Set PrjApp = New MSProject.Application — YowE3K
– YowE3K, Commented May 16, 2017 at 22:16
In that case, try Set PrjApp = CreateObject("Msproject.Application"). (I assume you are inserting these lines somewhere before the PrjApp.Application.FileOpenEx PrjFullName line). And I would still get rid of the Application from that line - it wouldn't be necessary, and it might generate an error - but probably not an error 91. — YowE3K
– YowE3K, Commented May 16, 2017 at 22:23
Both of those ways of creating the Project instance should work, and if they don't, they should throw errors. It's difficult to imagine what's going on in your case. It would help to update your code to a version which includes creating a Project application instance so we can try to replicate the issue using your exact code. — Tim Williams
– Tim Williams, Commented May 17, 2017 at 0:29

Jerred S. · Accepted Answer · 2017-05-17 17:09:23Z

0

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

answered May 17, 2017 at 17:09

Jerred S.

3761 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ERKSMTY Over a year ago

Thanks for the suggestion, but that isn't working. See the comments on the previous answer.

ERKSMTY · Accepted Answer · 2017-05-19 16:39:39Z

0

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!

answered May 19, 2017 at 16:39

ERKSMTY

1354 silver badges13 bronze badges

Collectives™ on Stack Overflow

Referencing MSProject in Excel VBA produces Runtime Error 91 object variable or with block variable not set

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related