|
View Full Version : rate changes on effecitve date - print out
Peggy Cornett 06-25-2007, 01:40 AM toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
Does any have VBA code to get a list that includes all enterprise resources,
their resource code, and their hourly rate in one file from the enterprise
resource pool? I am changing rates on an effective date and I need three
columns to show hourly
rates, one for each of the next three years. This information is in the cost
table for each resource. We want to do some comparisons.
Thanks,
~Peg Cornett
Rod Gill 06-25-2007, 04:32 AM Hi Peggy,
The following runs on the Enterprise Resource Pool (must be open and active
in Project Pro)
Sub ResourceDetails()
Dim Res As Resource
Dim Pay As PayRate
Dim str As String
For Each Res In ActiveProject.Resources
If Not Res Is Nothing Then
str = Res.Name & ", " & Res.Code
For Each Pay In Res.PayRates
str = str & ", " & Format(Pay.EffectiveDate, "Short Date") &
"-" & Pay.StandardRate
Next Pay
Debug.Print str
End If
Next Res
End Sub
Produces the following in the Immediate Window:
Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
1/01/2009-$121.00/h
Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
1/01/2009-$121.00/h
Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
1/01/2009-$121.00/h
--
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
----------------------------------------------------------------------------------------------------
"Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
> toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
> Does any have VBA code to get a list that includes all enterprise
> resources,
> their resource code, and their hourly rate in one file from the enterprise
> resource pool? I am changing rates on an effective date and I need three
> columns to show hourly
> rates, one for each of the next three years. This information is in the
> cost
> table for each resource. We want to do some comparisons.
> Thanks,
> ~Peg Cornett
>
>
Peggy Cornett 06-25-2007, 05:28 PM I opened the Enterprise Resource Pool, selected all resources read only to
view. I then copied the code below into VB Editor, pressed F8 to step through
the code and got a compile error on str = str & ", " &
Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
~Peg
"Rod Gill" wrote:
> Hi Peggy,
>
> The following runs on the Enterprise Resource Pool (must be open and active
> in Project Pro)
>
> Sub ResourceDetails()
> Dim Res As Resource
> Dim Pay As PayRate
> Dim str As String
> For Each Res In ActiveProject.Resources
> If Not Res Is Nothing Then
> str = Res.Name & ", " & Res.Code
> For Each Pay In Res.PayRates
> str = str & ", " & Format(Pay.EffectiveDate, "Short Date") &
> "-" & Pay.StandardRate
> Next Pay
> Debug.Print str
> End If
> Next Res
> End Sub
>
>
> Produces the following in the Immediate Window:
> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> 1/01/2009-$121.00/h
> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> 1/01/2009-$121.00/h
> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> 1/01/2009-$121.00/h
>
>
> --
>
> 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
>
>
> ----------------------------------------------------------------------------------------------------
>
>
> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
> > Does any have VBA code to get a list that includes all enterprise
> > resources,
> > their resource code, and their hourly rate in one file from the enterprise
> > resource pool? I am changing rates on an effective date and I need three
> > columns to show hourly
> > rates, one for each of the next three years. This information is in the
> > cost
> > table for each resource. We want to do some comparisons.
> > Thanks,
> > ~Peg Cornett
> >
> >
>
>
>
Rod Gill 06-25-2007, 10:43 PM Hi Peg,
That code worked fine in Project 2003 Prof SP2. Try just using:
str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
If this does not work, try deleting more and more of the line until it does
work! In addition under Tools, References, are there any missing references?
--
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
----------------------------------------------------------------------------------------------------
"Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
>I opened the Enterprise Resource Pool, selected all resources read only to
> view. I then copied the code below into VB Editor, pressed F8 to step
> through
> the code and got a compile error on str = str & ", " &
> Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
> ~Peg
>
> "Rod Gill" wrote:
>
>> Hi Peggy,
>>
>> The following runs on the Enterprise Resource Pool (must be open and
>> active
>> in Project Pro)
>>
>> Sub ResourceDetails()
>> Dim Res As Resource
>> Dim Pay As PayRate
>> Dim str As String
>> For Each Res In ActiveProject.Resources
>> If Not Res Is Nothing Then
>> str = Res.Name & ", " & Res.Code
>> For Each Pay In Res.PayRates
>> str = str & ", " & Format(Pay.EffectiveDate, "Short
>> Date") &
>> "-" & Pay.StandardRate
>> Next Pay
>> Debug.Print str
>> End If
>> Next Res
>> End Sub
>>
>>
>> Produces the following in the Immediate Window:
>> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> 1/01/2009-$121.00/h
>> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> 1/01/2009-$121.00/h
>> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> 1/01/2009-$121.00/h
>>
>>
>> --
>>
>> 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
>>
>>
>> ----------------------------------------------------------------------------------------------------
>>
>>
>> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
>> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
>> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
>> > Does any have VBA code to get a list that includes all enterprise
>> > resources,
>> > their resource code, and their hourly rate in one file from the
>> > enterprise
>> > resource pool? I am changing rates on an effective date and I need
>> > three
>> > columns to show hourly
>> > rates, one for each of the next three years. This information is in
>> > the
>> > cost
>> > table for each resource. We want to do some comparisons.
>> > Thanks,
>> > ~Peg Cornett
>> >
>> >
>>
>>
>>
Peggy Cornett 06-26-2007, 11:52 PM My stars! With red face I confess, I was stepping through by pressing F8 and
totally drew a blank that that was the reason it was stopping! I realized
this before I even tried it again. I pressed run and of course it works just
like you said. Hopefully this confession will help others try new things and
take baby steps with VBA as I am beginning to do.
Now, I want to get this info into Excel rather than just the immediate
window. I can do that but probably not as efficient as you would suggest.
Is this where I would create a .txt file to hold the data?
Thanks for your patience and I hope you and others are chuckling with me.
Peggy
"Rod Gill" wrote:
> Hi Peg,
>
> That code worked fine in Project 2003 Prof SP2. Try just using:
>
> str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
>
> If this does not work, try deleting more and more of the line until it does
> work! In addition under Tools, References, are there any missing references?
> --
>
> 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
>
>
> ----------------------------------------------------------------------------------------------------
>
>
> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
> >I opened the Enterprise Resource Pool, selected all resources read only to
> > view. I then copied the code below into VB Editor, pressed F8 to step
> > through
> > the code and got a compile error on str = str & ", " &
> > Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
> > ~Peg
> >
> > "Rod Gill" wrote:
> >
> >> Hi Peggy,
> >>
> >> The following runs on the Enterprise Resource Pool (must be open and
> >> active
> >> in Project Pro)
> >>
> >> Sub ResourceDetails()
> >> Dim Res As Resource
> >> Dim Pay As PayRate
> >> Dim str As String
> >> For Each Res In ActiveProject.Resources
> >> If Not Res Is Nothing Then
> >> str = Res.Name & ", " & Res.Code
> >> For Each Pay In Res.PayRates
> >> str = str & ", " & Format(Pay.EffectiveDate, "Short
> >> Date") &
> >> "-" & Pay.StandardRate
> >> Next Pay
> >> Debug.Print str
> >> End If
> >> Next Res
> >> End Sub
> >>
> >>
> >> Produces the following in the Immediate Window:
> >> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >>
> >>
> >> --
> >>
> >> 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
> >>
> >>
> >> ----------------------------------------------------------------------------------------------------
> >>
> >>
> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> >> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
> >> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
> >> > Does any have VBA code to get a list that includes all enterprise
> >> > resources,
> >> > their resource code, and their hourly rate in one file from the
> >> > enterprise
> >> > resource pool? I am changing rates on an effective date and I need
> >> > three
> >> > columns to show hourly
> >> > rates, one for each of the next three years. This information is in
> >> > the
> >> > cost
> >> > table for each resource. We want to do some comparisons.
> >> > Thanks,
> >> > ~Peg Cornett
> >> >
> >> >
> >>
> >>
> >>
>
>
>
Peggy Cornett 06-27-2007, 12:16 PM I did remove ""-" &" from before Pay.StandardRate.
Thanks for you help. This is great!
Peg
"Rod Gill" wrote:
> Hi Peg,
>
> That code worked fine in Project 2003 Prof SP2. Try just using:
>
> str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
>
> If this does not work, try deleting more and more of the line until it does
> work! In addition under Tools, References, are there any missing references?
> --
>
> 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
>
>
> ----------------------------------------------------------------------------------------------------
>
>
> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
> >I opened the Enterprise Resource Pool, selected all resources read only to
> > view. I then copied the code below into VB Editor, pressed F8 to step
> > through
> > the code and got a compile error on str = str & ", " &
> > Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
> > ~Peg
> >
> > "Rod Gill" wrote:
> >
> >> Hi Peggy,
> >>
> >> The following runs on the Enterprise Resource Pool (must be open and
> >> active
> >> in Project Pro)
> >>
> >> Sub ResourceDetails()
> >> Dim Res As Resource
> >> Dim Pay As PayRate
> >> Dim str As String
> >> For Each Res In ActiveProject.Resources
> >> If Not Res Is Nothing Then
> >> str = Res.Name & ", " & Res.Code
> >> For Each Pay In Res.PayRates
> >> str = str & ", " & Format(Pay.EffectiveDate, "Short
> >> Date") &
> >> "-" & Pay.StandardRate
> >> Next Pay
> >> Debug.Print str
> >> End If
> >> Next Res
> >> End Sub
> >>
> >>
> >> Produces the following in the Immediate Window:
> >> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >>
> >>
> >> --
> >>
> >> 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
> >>
> >>
> >> ----------------------------------------------------------------------------------------------------
> >>
> >>
> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> >> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
> >> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
> >> > Does any have VBA code to get a list that includes all enterprise
> >> > resources,
> >> > their resource code, and their hourly rate in one file from the
> >> > enterprise
> >> > resource pool? I am changing rates on an effective date and I need
> >> > three
> >> > columns to show hourly
> >> > rates, one for each of the next three years. This information is in
> >> > the
> >> > cost
> >> > table for each resource. We want to do some comparisons.
> >> > Thanks,
> >> > ~Peg Cornett
> >> >
> >> >
> >>
> >>
> >>
>
>
>
Peggy Cornett 07-03-2007, 12:18 AM In my enterprise resource pool I have 804 active resources. When I run the
macro the return in the immediate window begins with resource line 606 and
ends with 808. Why aren't the first 605 being returned?
Thanks!
Peggy
"Rod Gill" wrote:
> Hi Peg,
>
> That code worked fine in Project 2003 Prof SP2. Try just using:
>
> str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
>
> If this does not work, try deleting more and more of the line until it does
> work! In addition under Tools, References, are there any missing references?
> --
>
> 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
>
>
> ----------------------------------------------------------------------------------------------------
>
>
> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
> >I opened the Enterprise Resource Pool, selected all resources read only to
> > view. I then copied the code below into VB Editor, pressed F8 to step
> > through
> > the code and got a compile error on str = str & ", " &
> > Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
> > ~Peg
> >
> > "Rod Gill" wrote:
> >
> >> Hi Peggy,
> >>
> >> The following runs on the Enterprise Resource Pool (must be open and
> >> active
> >> in Project Pro)
> >>
> >> Sub ResourceDetails()
> >> Dim Res As Resource
> >> Dim Pay As PayRate
> >> Dim str As String
> >> For Each Res In ActiveProject.Resources
> >> If Not Res Is Nothing Then
> >> str = Res.Name & ", " & Res.Code
> >> For Each Pay In Res.PayRates
> >> str = str & ", " & Format(Pay.EffectiveDate, "Short
> >> Date") &
> >> "-" & Pay.StandardRate
> >> Next Pay
> >> Debug.Print str
> >> End If
> >> Next Res
> >> End Sub
> >>
> >>
> >> Produces the following in the Immediate Window:
> >> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> 1/01/2009-$121.00/h
> >>
> >>
> >> --
> >>
> >> 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
> >>
> >>
> >> ----------------------------------------------------------------------------------------------------
> >>
> >>
> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> >> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
> >> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
> >> > Does any have VBA code to get a list that includes all enterprise
> >> > resources,
> >> > their resource code, and their hourly rate in one file from the
> >> > enterprise
> >> > resource pool? I am changing rates on an effective date and I need
> >> > three
> >> > columns to show hourly
> >> > rates, one for each of the next three years. This information is in
> >> > the
> >> > cost
> >> > table for each resource. We want to do some comparisons.
> >> > Thanks,
> >> > ~Peg Cornett
> >> >
> >> >
> >>
> >>
> >>
>
>
>
Rod Gill 07-03-2007, 01:47 AM You can either create a text file, or paste straight into Excel using
Automation.
--
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
----------------------------------------------------------------------------------------------------
"Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
news:A37678F0-0B85-4FA7-B5D7-7B088B8AF3C2@microsoft.com...
> My stars! With red face I confess, I was stepping through by pressing F8
> and
> totally drew a blank that that was the reason it was stopping! I realized
> this before I even tried it again. I pressed run and of course it works
> just
> like you said. Hopefully this confession will help others try new things
> and
> take baby steps with VBA as I am beginning to do.
>
> Now, I want to get this info into Excel rather than just the immediate
> window. I can do that but probably not as efficient as you would suggest.
> Is this where I would create a .txt file to hold the data?
>
> Thanks for your patience and I hope you and others are chuckling with me.
> Peggy
>
> "Rod Gill" wrote:
>
>> Hi Peg,
>>
>> That code worked fine in Project 2003 Prof SP2. Try just using:
>>
>> str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
>>
>> If this does not work, try deleting more and more of the line until it
>> does
>> work! In addition under Tools, References, are there any missing
>> references?
>> --
>>
>> 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
>>
>>
>> ----------------------------------------------------------------------------------------------------
>>
>>
>> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
>> news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
>> >I opened the Enterprise Resource Pool, selected all resources read only
>> >to
>> > view. I then copied the code below into VB Editor, pressed F8 to step
>> > through
>> > the code and got a compile error on str = str & ", " &
>> > Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
>> > ~Peg
>> >
>> > "Rod Gill" wrote:
>> >
>> >> Hi Peggy,
>> >>
>> >> The following runs on the Enterprise Resource Pool (must be open and
>> >> active
>> >> in Project Pro)
>> >>
>> >> Sub ResourceDetails()
>> >> Dim Res As Resource
>> >> Dim Pay As PayRate
>> >> Dim str As String
>> >> For Each Res In ActiveProject.Resources
>> >> If Not Res Is Nothing Then
>> >> str = Res.Name & ", " & Res.Code
>> >> For Each Pay In Res.PayRates
>> >> str = str & ", " & Format(Pay.EffectiveDate, "Short
>> >> Date") &
>> >> "-" & Pay.StandardRate
>> >> Next Pay
>> >> Debug.Print str
>> >> End If
>> >> Next Res
>> >> End Sub
>> >>
>> >>
>> >> Produces the following in the Immediate Window:
>> >> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> >> 1/01/2009-$121.00/h
>> >> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> >> 1/01/2009-$121.00/h
>> >> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> >> 1/01/2009-$121.00/h
>> >>
>> >>
>> >> --
>> >>
>> >> 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
>> >>
>> >>
>> >> ----------------------------------------------------------------------------------------------------
>> >>
>> >>
>> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in
>> >> message
>> >> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
>> >> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
>> >> > Does any have VBA code to get a list that includes all enterprise
>> >> > resources,
>> >> > their resource code, and their hourly rate in one file from the
>> >> > enterprise
>> >> > resource pool? I am changing rates on an effective date and I need
>> >> > three
>> >> > columns to show hourly
>> >> > rates, one for each of the next three years. This information is in
>> >> > the
>> >> > cost
>> >> > table for each resource. We want to do some comparisons.
>> >> > Thanks,
>> >> > ~Peg Cornett
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
Rod Gill 07-03-2007, 01:49 AM The macro should run on every Resource open in the Enterprise Resource
project. So were all 804 resources open?
Alternatively I think the code only adds a line if there is a pay rate, so
resources with no pay rate won't show.
--
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
----------------------------------------------------------------------------------------------------
"Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
news:BA5C9541-A376-4387-BEEB-899413A3A0F0@microsoft.com...
> In my enterprise resource pool I have 804 active resources. When I run
> the
> macro the return in the immediate window begins with resource line 606 and
> ends with 808. Why aren't the first 605 being returned?
> Thanks!
> Peggy
>
> "Rod Gill" wrote:
>
>> Hi Peg,
>>
>> That code worked fine in Project 2003 Prof SP2. Try just using:
>>
>> str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
>>
>> If this does not work, try deleting more and more of the line until it
>> does
>> work! In addition under Tools, References, are there any missing
>> references?
>> --
>>
>> 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
>>
>>
>> ----------------------------------------------------------------------------------------------------
>>
>>
>> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
>> news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
>> >I opened the Enterprise Resource Pool, selected all resources read only
>> >to
>> > view. I then copied the code below into VB Editor, pressed F8 to step
>> > through
>> > the code and got a compile error on str = str & ", " &
>> > Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
>> > ~Peg
>> >
>> > "Rod Gill" wrote:
>> >
>> >> Hi Peggy,
>> >>
>> >> The following runs on the Enterprise Resource Pool (must be open and
>> >> active
>> >> in Project Pro)
>> >>
>> >> Sub ResourceDetails()
>> >> Dim Res As Resource
>> >> Dim Pay As PayRate
>> >> Dim str As String
>> >> For Each Res In ActiveProject.Resources
>> >> If Not Res Is Nothing Then
>> >> str = Res.Name & ", " & Res.Code
>> >> For Each Pay In Res.PayRates
>> >> str = str & ", " & Format(Pay.EffectiveDate, "Short
>> >> Date") &
>> >> "-" & Pay.StandardRate
>> >> Next Pay
>> >> Debug.Print str
>> >> End If
>> >> Next Res
>> >> End Sub
>> >>
>> >>
>> >> Produces the following in the Immediate Window:
>> >> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> >> 1/01/2009-$121.00/h
>> >> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> >> 1/01/2009-$121.00/h
>> >> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
>> >> 1/01/2009-$121.00/h
>> >>
>> >>
>> >> --
>> >>
>> >> 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
>> >>
>> >>
>> >> ----------------------------------------------------------------------------------------------------
>> >>
>> >>
>> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in
>> >> message
>> >> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
>> >> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
>> >> > Does any have VBA code to get a list that includes all enterprise
>> >> > resources,
>> >> > their resource code, and their hourly rate in one file from the
>> >> > enterprise
>> >> > resource pool? I am changing rates on an effective date and I need
>> >> > three
>> >> > columns to show hourly
>> >> > rates, one for each of the next three years. This information is in
>> >> > the
>> >> > cost
>> >> > table for each resource. We want to do some comparisons.
>> >> > Thanks,
>> >> > ~Peg Cornett
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
Peggy Cornett 07-03-2007, 02:42 PM Yes, all 804 resources are open. Yes, they have a pay rate. I ran the code
multiple times and get the same results. When I include the inactive
resources there are 1008 total. The results from the code still returns only
partial but is consistant if I re-run it. Note, regardless of what resources
I run it against I only get 199 resources in the result and they are the last
199 listed rather than the first.
Thanks for your help.
Peggy
"Rod Gill" wrote:
> The macro should run on every Resource open in the Enterprise Resource
> project. So were all 804 resources open?
>
> Alternatively I think the code only adds a line if there is a pay rate, so
> resources with no pay rate won't show.
>
> --
>
> 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
>
>
> ----------------------------------------------------------------------------------------------------
>
>
> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> news:BA5C9541-A376-4387-BEEB-899413A3A0F0@microsoft.com...
> > In my enterprise resource pool I have 804 active resources. When I run
> > the
> > macro the return in the immediate window begins with resource line 606 and
> > ends with 808. Why aren't the first 605 being returned?
> > Thanks!
> > Peggy
> >
> > "Rod Gill" wrote:
> >
> >> Hi Peg,
> >>
> >> That code worked fine in Project 2003 Prof SP2. Try just using:
> >>
> >> str = str & ", " & CStr(Pay.EffectiveDate) & "-" & Pay.StandardRate
> >>
> >> If this does not work, try deleting more and more of the line until it
> >> does
> >> work! In addition under Tools, References, are there any missing
> >> references?
> >> --
> >>
> >> 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
> >>
> >>
> >> ----------------------------------------------------------------------------------------------------
> >>
> >>
> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in message
> >> news:AFBF8A76-C846-4A91-AE7A-32B005081EC7@microsoft.com...
> >> >I opened the Enterprise Resource Pool, selected all resources read only
> >> >to
> >> > view. I then copied the code below into VB Editor, pressed F8 to step
> >> > through
> >> > the code and got a compile error on str = str & ", " &
> >> > Format(Pay.EffectiveDate, "Short Date") &. What did I miss?
> >> > ~Peg
> >> >
> >> > "Rod Gill" wrote:
> >> >
> >> >> Hi Peggy,
> >> >>
> >> >> The following runs on the Enterprise Resource Pool (must be open and
> >> >> active
> >> >> in Project Pro)
> >> >>
> >> >> Sub ResourceDetails()
> >> >> Dim Res As Resource
> >> >> Dim Pay As PayRate
> >> >> Dim str As String
> >> >> For Each Res In ActiveProject.Resources
> >> >> If Not Res Is Nothing Then
> >> >> str = Res.Name & ", " & Res.Code
> >> >> For Each Pay In Res.PayRates
> >> >> str = str & ", " & Format(Pay.EffectiveDate, "Short
> >> >> Date") &
> >> >> "-" & Pay.StandardRate
> >> >> Next Pay
> >> >> Debug.Print str
> >> >> End If
> >> >> Next Res
> >> >> End Sub
> >> >>
> >> >>
> >> >> Produces the following in the Immediate Window:
> >> >> Resource A, Code A, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> >> 1/01/2009-$121.00/h
> >> >> Resource B, Code B, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> >> 1/01/2009-$121.00/h
> >> >> Resource C, Code C, 1/01/1984-$100.00/h, 1/01/2008-$110.00/h,
> >> >> 1/01/2009-$121.00/h
> >> >>
> >> >>
> >> >> --
> >> >>
> >> >> 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
> >> >>
> >> >>
> >> >> ----------------------------------------------------------------------------------------------------
> >> >>
> >> >>
> >> >> "Peggy Cornett" <PeggyCornett@discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:29036EE7-6824-42A9-8346-18DB9FA61B62@microsoft.com...
> >> >> > toMS Project 2003 (11.2.2005.1801.15) SP2 Project Server 2003 SP2
> >> >> > Does any have VBA code to get a list that includes all enterprise
> >> >> > resources,
> >> >> > their resource code, and their hourly rate in one file from the
> >> >> > enterprise
> >> >> > resource pool? I am changing rates on an effective date and I need
> >> >> > three
> >> >> > columns to show hourly
> >> >> > rates, one for each of the next three years. This information is in
> >> >> > the
> >> >> > cost
> >> >> > table for each resource. We want to do some comparisons.
> >> >> > Thanks,
> >> >> > ~Peg Cornett
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
|
|