Import Resource information into Effective Date and Standard Rate ??
Import Resource information into Effective Date and Standard Rate ??
(OP)
Hello,
I am new to MS Project (2007) and learning what I can out of text books and on the internet. I have been asked, among other things, to create a resource sheet in project that includes employee information for my company. I used the import wizard to import pertinent data from excel to project.
The problem that I am having is this. My company is contracted to do multi-year jobs by our customers. Over the course of these jobs, our employees (resources) receive raises. Short of manual entry, which would be extremely laborious for a company this size, is there any way for me to change the Effective Date, Standard Rate and Overtime Rate cells accessed by Resource Sheet > Resource Information > Costs.
Thanks in advance,
Scott
I am new to MS Project (2007) and learning what I can out of text books and on the internet. I have been asked, among other things, to create a resource sheet in project that includes employee information for my company. I used the import wizard to import pertinent data from excel to project.
The problem that I am having is this. My company is contracted to do multi-year jobs by our customers. Over the course of these jobs, our employees (resources) receive raises. Short of manual entry, which would be extremely laborious for a company this size, is there any way for me to change the Effective Date, Standard Rate and Overtime Rate cells accessed by Resource Sheet > Resource Information > Costs.
Thanks in advance,
Scott
RE: Import Resource information into Effective Date and Standard Rate ??
RE: Import Resource information into Effective Date and Standard Rate ??
Thanks
RE: Import Resource information into Effective Date and Standard Rate ??
CODE
Dim res As Resource
Dim iCostRateTables As Integer
Dim iEntries As Integer
For Each res In ActiveProject.Resources
Debug.Print res.Name; " "
For iCostRateTables = 1 To 5 'Rate A (default); B; C; D; E
For iEntries = 1 To res.CostRateTables(iCostRateTables).PayRates.Count
Debug.Print "Rate "; iCostRateTables; " row "; iEntries; " Eff Date:"; _
Format(res.CostRateTables(iCostRateTables).PayRates(iEntries).EffectiveDate, "YYYY MMM dd"); " ";
Debug.Print " Std Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).StandardRate; " ";
Debug.Print " OVT Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).OvertimeRate
Next
Debug.Print
Next
Debug.Print: Debug.Print
Next
End Sub
RE: Import Resource information into Effective Date and Standard Rate ??
Hi,
I know next to nothing about MS Project. However, I am quite conversant with VBA, and may be able to shed some light on some general things.
In the VB editor there is an Object Browser. You'll find some help there. Here's what I found...
There is a Resources Collection containing Resource objects.
For any Resource object there is a StandardRate property and an OvertimeRate property.
As far as dates, I found Date1 thru Date10, Start1 thru Start10 & Finish1 thru Finish10.
I suppose that you could do something like this...
CODE
For i = 1 to ListLimit
'pseudo code here, that you figure out
sRES = GetResourceNameFromMyList(i)
for each RES in ActiveProject.Resources
if sRES = RES.Name then
RES.StandardRate = GetResource_STD_RATE_FromMyList(i)
RES.OvertimeRate = GetResource_OT_RATE_FromMyList(i)
Exit For
end if
Next
next
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Import Resource information into Effective Date and Standard Rate ??
I pasted your code into a macro file and ran it but I have a feeling that the field names on my resource sheet did not quite match up to the names in the code. I went ahead and changed the column headers to Std Rate and OVT Rate but still didn't get an additional row in Resource Cost with the updated information like I was hoping. Please forgive my lack of knowledge in programming language. Any additional pointers would be most welcome. And again, thank you for your time.
RE: Import Resource information into Effective Date and Standard Rate ??
RE: Import Resource information into Effective Date and Standard Rate ??
Sub PDQBach()
Dim res As Resource
Dim iCostRateTables As Integer
Dim iEntries As Integer
For Each res In ActiveProject.Resources
Debug.Print res.Name; " "
For iCostRateTables = 1 To 5 'Rate A (default); B; C; D; E
For iEntries = 1 To res.CostRateTables(iCostRateTables).PayRates.Count
Debug.Print "Rate "; iCostRateTables; " row "; iEntries; " Eff Date:"; _
Format(res.CostRateTables(iCostRateTables).PayRates(iEntries).EffectiveDate, "YYYY MMM dd"); " ";
Debug.Print " Std Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).StandardRate; " ";
Debug.Print " OVT Rate:"; res.CostRateTables(iCostRateTables).PayRates(iEntries).OvertimeRate
Next
Debug.Print
Next
Debug.Print: Debug.Print
Next
End Sub
RE: Import Resource information into Effective Date and Standard Rate ??
I have no idea what you're feeling but the code I gave you works for P2007 (and P2003 and, quite likely, P2002, P2000, and P98).
Which fields are you not seeing that you expected to see? What values are you not seeing that you expected to see?
RE: Import Resource information into Effective Date and Standard Rate ??
RE: Import Resource information into Effective Date and Standard Rate ??
Now I see that what you really have intended is to have code move already capture dates-and-rate-changes information and use it to update a couple of cost fields (Standard Rate, Overtime Rate) on View > Resource Sheet.
Why?
Project will automatically update the two Rate fields on the Effective date.
You say it will be extremely laborious ... what's laborious waiting for time to pass?
RE: Import Resource information into Effective Date and Standard Rate ??
In order to get project to update a resource's standard and overtime rates on a future effective date, you have to double click on a resource, go to the cost tab and manually enter those three fields. I would like to bypass this step and use a macro to automatically pull the data and fill in these fields for me to avoid that portion of the manually entry. I realize that on the effective date project will automatically use the correct values.
Am I missing something simple here?
Thanks
RE: Import Resource information into Effective Date and Standard Rate ??
CODE
Sub PDQBach2()
Dim oRes As Resource
Dim oCRT As CostRateTable
Dim msXLapp As Object
Dim iRow As Integer
Dim varOpenDataFile As Variant
Dim dteTemp As Date
Dim strTempStd As String, strTempOVT As String, strTempUse As String
'Load Excel
On Error Resume Next
Set msXLapp = GetObject(, "Excel.Application")
If Err.Number = 0 Then
msXLapp.Application.Quit
Set msXLapp = Nothing
Set msXLapp = CreateObject("Excel.Application")
Else
Set msXLapp = CreateObject("Excel.Application")
End If
msXLapp.Visible = True
On Error GoTo 0
'Open spreadsheet with data
'ColA = resource name
'ColB = a valid date field
'ColC = "$", amount, "/", "h" - e.g. $500/h
' h = hour
' d = day
' m = month
'ColD = same as format as colC
'ColE = "$", amount - e.g. $0
varOpenDataFile = msXLapp.GetOpenFilename("Excel workbooks (*.xls), *.xls", , "Open (DataFile)")
If varOpenDataFile = False Then
End
End If
msXLapp.Workbooks.Open FileName:=varOpenDataFile
msXLapp.Sheets(1).Activate
'Loop through each MS Project Resource
For Each oRes In ActiveProject.Resources
'Brute force lookup loop to find matching resource name in Excel spreadsheet
For iRow = 1 To msXLapp.ActiveSheet.cells.SpecialCells(xlCellTypeLastCell).Row
If msXLapp.cells(iRow, 1) = oRes.Name Then
'Choose "A" (Default) rate table
Set oCRT = oRes.CostRateTables("A")
dteTemp = msXLapp.cells(iRow, 2)
strTempStd = msXLapp.cells(iRow, 3)
strTempOVT = msXLapp.cells(iRow, 4)
strTempUse = msXLapp.cells(iRow, 5)
'Params are: effective date (mm/dd/yyyy), StdRate (d=day; h=hour; m=month), OVTRate, CostPerUseRate
'For example:
'oCRT.PayRates.Add "07/01/2012", "$500/h", "$600/h", "$0"
oCRT.PayRates.Add CStr(dteTemp), strTempStd, strTempOVT, strTempUse
End If
Next
Next
MsgBox "Done"
End Sub
RE: Import Resource information into Effective Date and Standard Rate ??
FYI
Although your Excel row number in your Excel Sheet used range may not exceed 32767, if it ever does, your iRow variable, declared as INTEGER, will overflow.
Best to declare all variables used for row numbers as LONG.
CODE
Skip,
Just traded in my old subtlety...
for a NUANCE!
RE: Import Resource information into Effective Date and Standard Rate ??
(In my defence here, it's somewhat unlikely that he will have more than 32,000 resources ... but in the general, yup, working with rows really should use a Long.)