Error changing Resource Booking Type through VBA

[Sean]

I'm working on a macro to close out a project. So far, I've had no
problem zeroing out remaining work. But we also want to change each
resource's booking type to 'Proposed'. I've tried various expressions
in VBA, but so far I've either gotten a 1004 error or a(n) 1101.
Here's the relevant snippets of my code:

Dim Rsrc As Resource

' Set Resource Booking Type to Proposed
For Each Rsrc In ActiveProject.Resources
ActiveProject.Resources(Rsrc).bookingtype =
pjBookingTypeProposed
MsgBox (Rsrc.Name)
Next Rsrc

You'll notice that it doesn't want to capitalize the "bookingtype"
portion, even though it recognizes it and prompts me for the
pjBookingTypeProposed. I tried using the SetField method, but those
only gave me 1101 errors. this one gives me the run-time error 1004
"an unexpected error occurred with the method."

Based on other threads here, I couldn't figure out why this code did
not work. Any thoughts?

Other threads on topic:
http://groups.google.com/group/micr...lnk=gst&q=bookingtype&rnum=1#337489bdfbf94c04
http://groups.google.com/group/micr...lnk=gst&q=bookingtype&rnum=9#df7fb619b17aceae

Thank you!

 

[Rod Gill]

Hi,

No comment on the bookingtype property as I'm not in a position to test at
the moment, but your code should read:
Dim Rsrc As Resource

' Set Resource Booking Type to Proposed
For Each Rsrc In ActiveProject.Resources
if not Rsrc is nothing then 'Test for blank resource
Rsrc.bookingtype = pjBookingTypeProposed
MsgBox (Rsrc.Name)
End If
Next Rsrc


--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Sean]

Thanks Rod. Good coding practice is probably something to start
with Hope there is still a resolution for my error.

--Sean

 

[Rod Gill]

So what error do you get running my code. Your original code can't work
because of the use of a resource object. Also, what version of Project
Server do you use?

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Sean]

I'll have to test the Code on Monday, since I'm away from the office
until then. I would expect that I'd get a 1004 error with yours
though, just as a guess. We're running Project Server 2003 SP2a I
believe.

--Sean

 

[Rod Gill]

That may be another cause of your problems. Unless you have the Enterprise
Resource Pool open, you cannot change and save Enterprise Resource details.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Sean]

Thanks for the help Rod. While you're new code still throws a 1004
error, you're probably right about the resource pool. I was thinking
that, since it was only for the project I wouldn't have to mess with
the server. But I forgot that you have to open the resource pool to
change the booking type through the Build Team from Enterprise dialog.
If you know how to open the pool in VBA, a code snippet would be nice.
But I'll give it a shot on my end and see what I come up with.

Again, thanks for the help and direction

--Sean

 

[Rod Gill]

Try recording a macro of you opening the resource pool manually.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Sean]

The macro I recorded had one line of code using the
EnterpriseResourcesOpen method, specifying the EUID for each resource
I select. But it opens the resource list of checked-out enterprise
resources. I'm not looking to do that though--I'm afraid it will
change the global user booking type, rather than the one used on the
specific project.

Other similar methods are EnterpriseResourceGet, which just adds a
resource to your resource pool, and EnterpriseResourcesImport. Neither
of those look promising. I played a little with the
EnterpriseResourceGet, but no luck there.

There is the option of simply opening the Build Team from Enterprise
dialog with the EnterpriseTeamBuilder method. I'll see if I have any
success with that.

Again, thanks for the help. I'll be sure to post any functional code I
end up with.

--Sean

 

[Sean]

Well I had no luck with anything involving the enterprise resource
pool, so instead I'm trying to edit the information as a column in the
Entry table of the Resource Sheet. So far the code seems to work very
nicely and does what I want. I'm not sure if it is very robust or has
good error handling though, so any thoughts on it would be nice.
Here's what I have:

Dim Rsrc As Resource
Dim RowCount As Integer

'Open Resource Pool to edit Booking Types
Application.ViewApply ("Resource Sheet")
Application.TableApply ("Entry")
Application.TableEdit Name:="Entry", TaskTable:=False,
NewFieldName:="BookingType", _
Title:="BookingType", ColumnPosition:=-1,
OverwriteExisting:=True
Application.TableApply ("Entry")

'Set Counter Variable
RowCount = 1

'Set Resource Booking Type to Proposed
For Each Rsrc In ActiveProject.Resources
If Not (Rsrc Is Nothing) Then 'test for blank resource
SelectResourceField Row:=RowCount, Column:="BookingType",
RowRelative:=False
SetResourceField Field:="BookingType", Value:="Proposed"
End If
RowCount = RowCount + 1
Next Rsrc

If ActiveProject.Resources.Count = RowCount - 1 Then
MsgBox ("All Resources Changed")
Else
MsgBox ("Please change resource bookings to 'Proposed'. ")
EnterpriseTeamBuilder
End If

'Hides the booking type column
SelectResourceColumn Column:="Booking Type"
ColumnDelete

When I get the whole thing polished, I'll post the 'Close Out Project'
macro code here. This was the hardest part though.

--Sean