Code stopping mid runtime with error

[Red]

Hi All,

Does anyone know why the below code terminates at the line [ If
Proj.Tasks.Count > 0 Then ] without error? The only way I can get it to run
is to break at the said line and step through manually. The purpose of the
code is to open a resource pool file in Read/Write Sharer mode (3rd option)
and export data to excel.

Thanks in advance


FileOpen "\\hta-nt1\Project_plans\Resource Pool\Definite
ResPool.mpp", , , _
, , , , , , , , pjPoolAndSharers
Set Proj = ActiveProject
Tcount = 0

'step through each open project and close until the resource pool
'sharer file is active
For Each Proj In application.Projects
If Not Proj Is Nothing Then
If Proj.Name <> "Definite ResPool" Then

'BREAK HERE ..... BREAK HERE !!!!

If Proj.Tasks.Count > 0 Then
'Export task details to excel except the ResPool
task
For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
If T.Name <> "ResPool" Then
If T.OutlineLevel = 1 Then
Set xlCol = xlRow.Offset(1, 0)
dwn 1
xlCol = T.Name
rgt 1
xlCol = T.PercentComplete / 100
rgt 1
End If
End If
Tcount = Tcount + 1
End If
Next T
End If
FileClose pjDoNotSave
End If
End If
Next Proj

 

[Rod Gill]

Hi,

Resource pools don't (and shouldn't) have tasks. Because there are no tasks,
there is no Tasks collection so even 0 can't be returned from the .count
property.

The only data you can read from a resource pool is assignment and resource
information.

--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[Red]

Hi Rod,

The resource pool is being opened as a sharer Read/Write which creates a
normal mpp file with all linked projects merged into one plan. It's this
plan that the task information is being exported from.

Red.

Hi,

Resource pools don't (and shouldn't) have tasks. Because there are no tasks,
there is no Tasks collection so even 0 can't be returned from the .count
property.

The only data you can read from a resource pool is assignment and resource
information.

--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

Hi All,

Does anyone know why the below code terminates at the line [ If
Proj.Tasks.Count > 0 Then ] without error? The only way I can get it to run
is to break at the said line and step through manually. The purpose of the
code is to open a resource pool file in Read/Write Sharer mode (3rd option)
and export data to excel.

Thanks in advance


FileOpen "\\hta-nt1\Project_plans\Resource Pool\Definite
ResPool.mpp", , , _
, , , , , , , , pjPoolAndSharers
Set Proj = ActiveProject
Tcount = 0

'step through each open project and close until the resource pool
'sharer file is active
For Each Proj In application.Projects
If Not Proj Is Nothing Then
If Proj.Name <> "Definite ResPool" Then

'BREAK HERE ..... BREAK HERE !!!!

If Proj.Tasks.Count > 0 Then
'Export task details to excel except the ResPool
task
For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
If T.Name <> "ResPool" Then
If T.OutlineLevel = 1 Then
Set xlCol = xlRow.Offset(1, 0)
dwn 1
xlCol = T.Name
rgt 1
xlCol = T.PercentComplete / 100
rgt 1
End If
End If
Tcount = Tcount + 1
End If
Next T
End If
FileClose pjDoNotSave
End If
End If
Next Proj

 

[JackD]

First, why are you setting Proj to ActiveProject?
Second, why isn't Application capitalized?

Perhaps you code stops because it encounters a project with zero tasks?
(like project 1)

It is hard to tell without the complete code.

-Jack