Sequential open and close of MS Peoject files from Excel

[rjdill]

On my job, I run many projects of short duration concurrently. In order to keep up with them I created an excel dashbaord showing about 30 key tasks from each project with information on the tasks, dedicating a coloumn to each project.

For the dashboard tab, I modified the excel context menu to provide a selection to open the actual msproject file from the dashboard, let me update something, then close save the msproject fle.

I added a macro to refresh the dashboard, re-reading all msproj files and repopulating the dashboard.

It all works great until I open an msproj file from th excel context menu.

After opening & closing an msproj file, the refresh consistently fails on the "For Each t In ActiveProject.Tasks" statement

When it fails,
ActiveProject.Tasks = <The remote server machine does not exist or is unavailable>

The code runs this way:

fileCon:
If pFile = "" Then GoTo Nextfile
Set Proj = CreateObject("MSProject.Application")
Proj.Visible = False

Rem OPEN NEW MS PROJECT FILE
Proj.FileOpenEx Name:=pFile
.
.
.
For Each t In ActiveProject.Tasks
If transCount = 0 Then resource = t.ResourceNames
transCount = transCount + 1

If Not t Is Nothing Then If t.Summary Then GoTo Nextt1
taskCount = taskCount + 1
.
.
.

Steppping through the code, I see that the file-open code is executed with no errors, then fails on "For Each t In ActiveProject.Tasks
..."

-----
I have noticed that after the initial populating of the dashboard, there is always a hidden instance of winproj.exe in the tasklist.

The vba close code is

Proj.FileCloseAllEx
Set Proj = Nothing

But, no matter what I do, I can only close this hidden instance of winproj.exe with EndProcess in Task Manager.

If anyone has helpful information, I would appreciate it.

 

[rjdill]

On my post, I didn't make this clear, but the initial populating of the dashboard is the result of sequentially opening, reading, and closing all the active msproject files.

This followed by refresh (re-open, re-read, re-close) works fine, but if I open the actual MSProj application from the context menu, and close, and then refresh, I get the error.

 

[PHV]

At least, replace this:
For Each t In ActiveProject.Tasks
with this:
For Each t In Proj.ActiveProject.Tasks

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rjdill]

Thanks PH. Although it didn't solve the problem, it did improve my code.