M
Mark Main
I've looked everywhere I can to find the "Generic" resource field in VBA. I
give up... anyone know where it is? Thanks for the help.
give up... anyone know where it is? Thanks for the help.
J
JulieS
Hello Mark,
See the EnterpriseGeneric property.
I hope this helps. Let us know how you get along.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
See the EnterpriseGeneric property.
I hope this helps. Let us know how you get along.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
M
Mark Main
I'm running MS Project 2003 (NON-server); I see Resource.EnterpirseGeneric as
a property, but I'm really looking for Resource.Generic and it's no where to
be found. I want to see the Generic field that is available on the Resource
Entry table. Example code often helps--I would like to have my code be
similar to below. Thanks again to all for any help.
Dim rsrc as Resource
For Each rsrc In ActiveProject.Resources
If rsrc.Generic = False Then
...
End If
Next rsrc
a property, but I'm really looking for Resource.Generic and it's no where to
be found. I want to see the Generic field that is available on the Resource
Entry table. Example code often helps--I would like to have my code be
similar to below. Thanks again to all for any help.
Dim rsrc as Resource
For Each rsrc In ActiveProject.Resources
If rsrc.Generic = False Then
...
End If
Next rsrc
J
JulieS
Hi Mark,
As far as I know, the "Generic" field in the resource sheet does not
have any effect when used in Project Professional not connected to a
server. You don't state what it is that you want to do with the value
of the "generic" field but have you considered using custom fields with
formulas? You can test the value of the generic in another field.
For example the following formula in a flag field test the value of the
generic field and sets the flag to yes if the generic field is set to
no.
IIf([Generic]=No,Yes,No)
Sorry I couldn't be of more assistance. Perhaps one of the other folks
will weigh in.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
As far as I know, the "Generic" field in the resource sheet does not
have any effect when used in Project Professional not connected to a
server. You don't state what it is that you want to do with the value
of the "generic" field but have you considered using custom fields with
formulas? You can test the value of the generic in another field.
For example the following formula in a flag field test the value of the
generic field and sets the flag to yes if the generic field is set to
no.
IIf([Generic]=No,Yes,No)
Sorry I couldn't be of more assistance. Perhaps one of the other folks
will weigh in.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
J
JulieS
Hello Mark,
In quick testing the following code reads the value of the generic
field. If the generic field is set to "yes" it displays "generic" in
the text1 field. If the generic field is set to "no" it displays "not
generic" in the text1 field.
Sub Generic()
Dim r As Resource
Dim rs As Resources
Set rs = ActiveProject.Resources
For Each r In rs
If Not r Is Nothing Then
If r.EnterpriseGeneric = 0 Then
r.Text1 = "Not generic"
Else
r.Text1 = "Generic"
End If
End If
Next r
End Sub
Tested in Project 2003 Professional without server connection.
I hope this helps. Let us know how you get along.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
In quick testing the following code reads the value of the generic
field. If the generic field is set to "yes" it displays "generic" in
the text1 field. If the generic field is set to "no" it displays "not
generic" in the text1 field.
Sub Generic()
Dim r As Resource
Dim rs As Resources
Set rs = ActiveProject.Resources
For Each r In rs
If Not r Is Nothing Then
If r.EnterpriseGeneric = 0 Then
r.Text1 = "Not generic"
Else
r.Text1 = "Generic"
End If
End If
Next r
End Sub
Tested in Project 2003 Professional without server connection.
I hope this helps. Let us know how you get along.
Julie
Project MVP
Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project
M
Mark Main
Thanks Julie! Yes, your code works. it's odd that
"Resources.EnterpriseGeneric" is the "Resources.Generic" field, but it works,
so thank you.
I wrote the following code in EXCEL that reads my project plan and creates a
vacation & holiday schedule. I hand this document out at my status meetings
so that people can see if we know (in the plan) about their vacation schedule
or not.
Option Explicit
Dim projApp As MSProject.Application
Dim weekendAdj, nameMax As Long
Dim abbvName(50), actlName(50) As String
Sub VacationTime()
'This code assumes that you have a worksheet named Resources, with the
'A1.value = "Project Abbv" and A2.value = "Actual Resource Name"
'and then you have your list of Names and abbreviated Names listed in
these columns
'ONLY names listed here will show up on the report in the "Resources"
worksheet
'This code assumes that you have a worksheet named "Resources" and this
is where
'your vacation and holiday schedule will be generated.
Dim xstart, xfinish As Date
Dim rsrc As Resource
Dim r, i, c1, c2, outlookWeeks As Long
Dim projPathname, tasklistPath, projName As String
Dim holiday As String
Dim xday As Date
LoadResourceNames 'Load actual names rather than their abbreviations
Sheets("Vacations").Activate
ActiveSheet.Unprotect
ActiveWindow.FreezePanes = False
'default all cells to right justified, Top alignment with Text Wrap
Cells.Select
Cells.Clear
'Setup title row
Cells(1, 1).Value = "Name"
Cells(1, 2).Value = "Vacation Date"
Cells(1, 3).Value = "Vacation Title"
'Open MS Project if it is not already open
On Error Resume Next
Set projApp = GetObject(, "MSProject.application")
If projApp Is Nothing Then
Set projApp = CreateObject("MSProject.application")
End If
On Error GoTo 0
'Open the project plan file and reset any group/sort settings
projName = "put the official full name of your project here"
projPathname = "C:\The\File\Pathname\Goes Here\"
projApp.FileOpen Name:=projPathname & "The Project Plan Name.mpp",
ReadOnly:=True
'Scan the plan and create Excel Timesheets
xstart = Date
xfinish = Int(ActiveProject.ProjectFinish)
Cells(1, 1) = "Name"
Cells(1, 2) = "Vacation Day"
r = 2
For Each rsrc In ActiveProject.Resources
If Not rsrc Is Nothing Then
For i = 0 To nameMax
If abbvName(i) = rsrc.Name Then Exit For
Next i
If rsrc.EnterpriseGeneric = False And i <= nameMax Then
For xday = xstart To xfinish
If rsrc.Calendar.Period(xday, xday).Working = False And
Weekday(xday, vbSaturday) > 2 Then
If ActiveProject.Calendar.Period(xday, xday).Working =
True Then
Cells(r, 1).Value = actlName(i)
Cells(r, 2).Value = Format(xday, "dddd mmmm dd, yyyy")
Cells(r, 3).Value = "Vacation Day"
r = r + 1
Else
If Hour(ActiveProject.Calendar.Period(xday,
xday).Shift1.Start) = 0 Then
'The Hour check above for Shift1 because we often
schedule only half a days work (only the first shift of 8:00 to Noon)
'the day before holidays to build in a buffer to
reflect reality
'the check above will NOT treat these half days
(which show up as Non-Working days) are bonified holidays
Cells(r, 1).Value = actlName(i)
Cells(r, 2).Value = Format(xday, "dddd mmmm dd, yyyy")
Cells(r, 3).Value = GetHoliday(xday)
r = r + 1
End If
End If
End If
Next xday
End If
End If
Next rsrc
MsgBox "Successful Complete."
End Sub
Function GetHoliday(ByRef xday As Date) As String
If Weekday(xday, vbSaturday) = 1 Then
weekendAdj = -1
ElseIf Weekday(xday, vbSunday) = 1 Then
weekendAdj = 1
Else
weekendAdj = 0
End If
GetHoliday = "Holiday"
'NEW YEARS DAY (JANUARY 1)
If weekendAdj < 0 And xday + weekendAdj = DateSerial(Year(xday +
weekendAdj), 12, 31) _
Or weekendAdj >= 0 And xday + weekendAdj = DateSerial(Year(xday +
weekendAdj), 1, 1 + weekendAdj) Then
GetHoliday = "New Years Day Holiday"
Exit Function
End If
'MARTIN LUTHER KING, JR. DAY (3RD MONDAY IN JANUARY)
If xday = DateSerial(Year(xday), 1, 22 - Weekday(DateSerial(Year(xday),
1, 1), vbTuesday)) Then
GetHoliday = "Martin Luther King, Jr. Holiday"
Exit Function
End If
'GEORGE WASHINGTON'S BIRTHDAY (3RD MONDAY IN FEBRUARY)
If xday = DateSerial(Year(xday), 2, 22 - Weekday(DateSerial(Year(xday),
2, 1), vbTuesday)) Then
GetHoliday = "George Washington's Birthday Holiday"
Exit Function
End If
'MEMORIAL DAY (LAST MONDAY IN MAY)
If xday = DateSerial(Year(xday), 5, 32 - Weekday(DateSerial(Year(xday),
5, 31), vbMonday)) Then
GetHoliday = "Memorial Day Holiday"
Exit Function
End If
'INDEPENDENCE DAY (JULY 4TH)
If xday + weekendAdj = DateSerial(Year(xday), 7, 4 + weekendAdj) Then
GetHoliday = "Independence Day Holiday"
Exit Function
End If
'LABOR DAY (1ST MONDAY IN SEPTEMBER)
If xday = DateSerial(Year(xday), 9, 8 - Weekday(DateSerial(Year(xday), 9,
1), vbTuesday)) Then
GetHoliday = "Labor Day Holiday"
Exit Function
End If
'COLUMBUS DAY (2ND MONDAY IN OCTOBER)
If xday = DateSerial(Year(xday), 10, 15 - Weekday(DateSerial(Year(xday),
10, 1), vbTuesday)) Then
GetHoliday = "Columbus Holiday"
Exit Function
End If
'VETERAN'S DAY (NOVEMBER 11TH)
If xday + weekendAdj = DateSerial(Year(xday), 11, 11 + weekendAdj) Then
GetHoliday = "Veteran's Day Holiday"
Exit Function
End If
'THANKSGIVING (4TH THURSDAY IN NOVEMBER)
If xday = DateSerial(Year(xday), 11, 29 - Weekday(DateSerial(Year(xday),
11, 1), vbFriday)) Then
GetHoliday = "Thanksgiving Holiday"
Exit Function
End If
'HOLIDAY SHOPPING DAY (4TH FRIDAY IN NOVEMBER)
If xday = DateSerial(Year(xday), 11, 30 - Weekday(DateSerial(Year(xday),
11, 1), vbFriday)) Then
GetHoliday = "Friday after Thanksgiving (not official holiday)"
Exit Function
End If
'CHRISTMAS EVE
If xday + weekendAdj = DateSerial(Year(xday), 12, 24 + weekendAdj) Then
GetHoliday = "Christmas Eve (Not official holiday)"
Exit Function
End If
'CHRISTMAS
If xday + weekendAdj = DateSerial(Year(xday), 12, 25 + weekendAdj) Then
GetHoliday = "Christmas Holiday"
Exit Function
End If
'NEW YEARS EVE
If xday + weekendAdj = DateSerial(Year(xday), 12, 31) Then
GetHoliday = "New Years Eve Holiday (not official holiday)"
Exit Function
End If
End Function
Sub LoadResourceNames()
'This loads the actual resource names and project plan abbreviated names
'so we're able to use their real name rather than an abbreviation on the
report.
nameMax = -1
Do
nameMax = nameMax + 1
abbvName(nameMax) = Sheets("Resources").Cells(nameMax + 2, 1)
actlName(nameMax) = Sheets("Resources").Cells(nameMax + 2, 2)
Loop Until Sheets("Resources").Cells(nameMax + 3, 1) = Empty
End Sub
"Resources.EnterpriseGeneric" is the "Resources.Generic" field, but it works,
so thank you.
I wrote the following code in EXCEL that reads my project plan and creates a
vacation & holiday schedule. I hand this document out at my status meetings
so that people can see if we know (in the plan) about their vacation schedule
or not.
Option Explicit
Dim projApp As MSProject.Application
Dim weekendAdj, nameMax As Long
Dim abbvName(50), actlName(50) As String
Sub VacationTime()
'This code assumes that you have a worksheet named Resources, with the
'A1.value = "Project Abbv" and A2.value = "Actual Resource Name"
'and then you have your list of Names and abbreviated Names listed in
these columns
'ONLY names listed here will show up on the report in the "Resources"
worksheet
'This code assumes that you have a worksheet named "Resources" and this
is where
'your vacation and holiday schedule will be generated.
Dim xstart, xfinish As Date
Dim rsrc As Resource
Dim r, i, c1, c2, outlookWeeks As Long
Dim projPathname, tasklistPath, projName As String
Dim holiday As String
Dim xday As Date
LoadResourceNames 'Load actual names rather than their abbreviations
Sheets("Vacations").Activate
ActiveSheet.Unprotect
ActiveWindow.FreezePanes = False
'default all cells to right justified, Top alignment with Text Wrap
Cells.Select
Cells.Clear
'Setup title row
Cells(1, 1).Value = "Name"
Cells(1, 2).Value = "Vacation Date"
Cells(1, 3).Value = "Vacation Title"
'Open MS Project if it is not already open
On Error Resume Next
Set projApp = GetObject(, "MSProject.application")
If projApp Is Nothing Then
Set projApp = CreateObject("MSProject.application")
End If
On Error GoTo 0
'Open the project plan file and reset any group/sort settings
projName = "put the official full name of your project here"
projPathname = "C:\The\File\Pathname\Goes Here\"
projApp.FileOpen Name:=projPathname & "The Project Plan Name.mpp",
ReadOnly:=True
'Scan the plan and create Excel Timesheets
xstart = Date
xfinish = Int(ActiveProject.ProjectFinish)
Cells(1, 1) = "Name"
Cells(1, 2) = "Vacation Day"
r = 2
For Each rsrc In ActiveProject.Resources
If Not rsrc Is Nothing Then
For i = 0 To nameMax
If abbvName(i) = rsrc.Name Then Exit For
Next i
If rsrc.EnterpriseGeneric = False And i <= nameMax Then
For xday = xstart To xfinish
If rsrc.Calendar.Period(xday, xday).Working = False And
Weekday(xday, vbSaturday) > 2 Then
If ActiveProject.Calendar.Period(xday, xday).Working =
True Then
Cells(r, 1).Value = actlName(i)
Cells(r, 2).Value = Format(xday, "dddd mmmm dd, yyyy")
Cells(r, 3).Value = "Vacation Day"
r = r + 1
Else
If Hour(ActiveProject.Calendar.Period(xday,
xday).Shift1.Start) = 0 Then
'The Hour check above for Shift1 because we often
schedule only half a days work (only the first shift of 8:00 to Noon)
'the day before holidays to build in a buffer to
reflect reality
'the check above will NOT treat these half days
(which show up as Non-Working days) are bonified holidays
Cells(r, 1).Value = actlName(i)
Cells(r, 2).Value = Format(xday, "dddd mmmm dd, yyyy")
Cells(r, 3).Value = GetHoliday(xday)
r = r + 1
End If
End If
End If
Next xday
End If
End If
Next rsrc
MsgBox "Successful Complete."
End Sub
Function GetHoliday(ByRef xday As Date) As String
If Weekday(xday, vbSaturday) = 1 Then
weekendAdj = -1
ElseIf Weekday(xday, vbSunday) = 1 Then
weekendAdj = 1
Else
weekendAdj = 0
End If
GetHoliday = "Holiday"
'NEW YEARS DAY (JANUARY 1)
If weekendAdj < 0 And xday + weekendAdj = DateSerial(Year(xday +
weekendAdj), 12, 31) _
Or weekendAdj >= 0 And xday + weekendAdj = DateSerial(Year(xday +
weekendAdj), 1, 1 + weekendAdj) Then
GetHoliday = "New Years Day Holiday"
Exit Function
End If
'MARTIN LUTHER KING, JR. DAY (3RD MONDAY IN JANUARY)
If xday = DateSerial(Year(xday), 1, 22 - Weekday(DateSerial(Year(xday),
1, 1), vbTuesday)) Then
GetHoliday = "Martin Luther King, Jr. Holiday"
Exit Function
End If
'GEORGE WASHINGTON'S BIRTHDAY (3RD MONDAY IN FEBRUARY)
If xday = DateSerial(Year(xday), 2, 22 - Weekday(DateSerial(Year(xday),
2, 1), vbTuesday)) Then
GetHoliday = "George Washington's Birthday Holiday"
Exit Function
End If
'MEMORIAL DAY (LAST MONDAY IN MAY)
If xday = DateSerial(Year(xday), 5, 32 - Weekday(DateSerial(Year(xday),
5, 31), vbMonday)) Then
GetHoliday = "Memorial Day Holiday"
Exit Function
End If
'INDEPENDENCE DAY (JULY 4TH)
If xday + weekendAdj = DateSerial(Year(xday), 7, 4 + weekendAdj) Then
GetHoliday = "Independence Day Holiday"
Exit Function
End If
'LABOR DAY (1ST MONDAY IN SEPTEMBER)
If xday = DateSerial(Year(xday), 9, 8 - Weekday(DateSerial(Year(xday), 9,
1), vbTuesday)) Then
GetHoliday = "Labor Day Holiday"
Exit Function
End If
'COLUMBUS DAY (2ND MONDAY IN OCTOBER)
If xday = DateSerial(Year(xday), 10, 15 - Weekday(DateSerial(Year(xday),
10, 1), vbTuesday)) Then
GetHoliday = "Columbus Holiday"
Exit Function
End If
'VETERAN'S DAY (NOVEMBER 11TH)
If xday + weekendAdj = DateSerial(Year(xday), 11, 11 + weekendAdj) Then
GetHoliday = "Veteran's Day Holiday"
Exit Function
End If
'THANKSGIVING (4TH THURSDAY IN NOVEMBER)
If xday = DateSerial(Year(xday), 11, 29 - Weekday(DateSerial(Year(xday),
11, 1), vbFriday)) Then
GetHoliday = "Thanksgiving Holiday"
Exit Function
End If
'HOLIDAY SHOPPING DAY (4TH FRIDAY IN NOVEMBER)
If xday = DateSerial(Year(xday), 11, 30 - Weekday(DateSerial(Year(xday),
11, 1), vbFriday)) Then
GetHoliday = "Friday after Thanksgiving (not official holiday)"
Exit Function
End If
'CHRISTMAS EVE
If xday + weekendAdj = DateSerial(Year(xday), 12, 24 + weekendAdj) Then
GetHoliday = "Christmas Eve (Not official holiday)"
Exit Function
End If
'CHRISTMAS
If xday + weekendAdj = DateSerial(Year(xday), 12, 25 + weekendAdj) Then
GetHoliday = "Christmas Holiday"
Exit Function
End If
'NEW YEARS EVE
If xday + weekendAdj = DateSerial(Year(xday), 12, 31) Then
GetHoliday = "New Years Eve Holiday (not official holiday)"
Exit Function
End If
End Function
Sub LoadResourceNames()
'This loads the actual resource names and project plan abbreviated names
'so we're able to use their real name rather than an abbreviation on the
report.
nameMax = -1
Do
nameMax = nameMax + 1
abbvName(nameMax) = Sheets("Resources").Cells(nameMax + 2, 1)
actlName(nameMax) = Sheets("Resources").Cells(nameMax + 2, 2)
Loop Until Sheets("Resources").Cells(nameMax + 3, 1) = Empty
End Sub
J
JulieS
Hello Mark,
Glad to know the code snippet worked and thanks for the feedback.
Thanks also for posting the code. I'm sure others will find it useful
Julie
Glad to know the code snippet worked and thanks for the feedback.
Thanks also for posting the code. I'm sure others will find it useful
Julie