Use VBA to update ActualWork

[JeffPoos]

Hi there i was wondering if it is possible to Upadate the actual work done for each resource using VBA. Here is my situation. I have an excel time sheet for each resource. I parse the excel workbook to find their task and name. I then parse through the project document and want to input their hours worked into the actaul work fields in the detail view. I have used the TimeScaleData to view their time for a current date but i cannot upadate it. I am a java developer who has been assigned to write this macro(Not super familiar with VB). Can anyone help. Do i need to explain better(not really familiar with the proper project lingo). Could some supply some snipits of vba code?

 

[John]

Jeff,
The explanation of your problem is fine. Yes you can use VBA to pull the
timesheet data from Excel and update resource Actual Work by time period
(i.e. hour, day, etc.). The macro can reside in either Excel or Project.
Basically the macro would gather the data from the timesheet, open
Project (if the macro resided in Excel), and export the timesphased
resource data to the Actual Work field.

Jack Dahlgren has several VBA examples on his webpage at:
http://masamiki.com/project/macros.htm
One of the macros, "Export hierarchy to Excel" has code that shows how
to open one app from another. You will need to use the TimeScaleData
method to transfer the data. You can find the full syntax for that
method in the VBA help file.

If you still have questions or problems after looking at Jack's macros,
I may have some macros that will help. They do use the TimeScaleData
method.

There is also a very good VBA training module on the MVP webpage at:
http://www.mvps.org/project/links.htm
Go to the bottom of the page and find, "Project 98 Visual Basic
Environment Training". Even though it says it is for Project 98, it is
equally applicable to all later versions of Project.

I might also note that if you have Project 2000 Project Central
available, I believe it has a timesheet capability. The server versions
of Project may also have that functionality.

Hope this helps.
John

 

[Rod Gill]

Hi,

Try Value=240. Value is a variant that is numeric if there is a value and
="" if not.

Your structure has one big weakness: If anyone changes a task or resource
name your code won't find a match between Excel data and Project, You also
need to handle new tasks being added and to different projects.

For reliability you really need to export Task and Resource Unique ID's to
Excel so that they can be used to import the data again, regardless of
whether or not names have changed.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

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

John thanks for the link. It helped a little but most of the code was

exporting and i am running into a problem importing from excel. I included
some of the code but am running into errors when i try to set values for
ActualWork.

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open("C:\project\Jeff Pusczek.xls")
Set xlSheet = xlBook.Sheets.Item("time")
sTask = xlSheet.Range("A3").value
sName = xlSheet.Range("B1").value
For Each tskT In ActiveProject.Tasks
If tskT.Name = sTask Then
MsgBox ("Found Task")
For Each rsSingleRs In tskT.Resources
If rsSingleRs.Name = sName Then
MsgBox ("Found Name")
Set oTsvs = rsSingleRs.TimeScaleData("12/24/02",

"12/24/02", pjResourceTimescaledActualWork, pjTimescaleDays)

For Each oTsv In oTsvs
MsgBox (oTsv.value & "m")
value = "240"
oTsv.value = value
Next oTsv
End If
Next rsSingleRs
End If
Next tskT

When i set oTsv.value = value i get an error. Am i at least on the right

track here?

 

[Jan De Messemaeker]

Hi Jeff,

Without actually taking the time to look iat your macro into detail, one
thing strikes me.
I've written this application already for 3 customers and I inevitably,
always, update the assignment object, never the resource object.
The actual work in a timesheet is per task per resource isn't it? If not I
fail to see how project could split the resource's work on any one day over
the different assignments it works on...

HTH

John thanks for the link. It helped a little but most of the code was

exporting and i am running into a problem importing from excel. I included
some of the code but am running into errors when i try to set values for
ActualWork.

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open("C:\project\Jeff Pusczek.xls")
Set xlSheet = xlBook.Sheets.Item("time")
sTask = xlSheet.Range("A3").value
sName = xlSheet.Range("B1").value
For Each tskT In ActiveProject.Tasks
If tskT.Name = sTask Then
MsgBox ("Found Task")
For Each rsSingleRs In tskT.Resources
If rsSingleRs.Name = sName Then
MsgBox ("Found Name")
Set oTsvs = rsSingleRs.TimeScaleData("12/24/02",

"12/24/02", pjResourceTimescaledActualWork, pjTimescaleDays)

For Each oTsv In oTsvs
MsgBox (oTsv.value & "m")
value = "240"
oTsv.value = value
Next oTsv
End If
Next rsSingleRs
End If
Next tskT

When i set oTsv.value = value i get an error. Am i at least on the right

track here?

 

[John]

Jeff,
Jan has a valid point. I also use the assignment object when working
with timescale data for resources. However, if your file is structured
such that the resource has only one assignment, then it probably should
work. I'd have to mock up a file and run your code to be sure I'm not
missing something basic. I'll try that this evening unless someone else
sees an obvious error and posts a reply.

It would be helpful to know exactly what the error message says. It also
appears that you are updating just one timesheet value. Is that true?

John

 

[John]

John thanks for the link. It helped a little but most of the code was
exporting and i am running into a problem importing from excel. I included
some of the code but am running into errors when i try to set values for
ActualWork.

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open("C:\project\Jeff Pusczek.xls")
Set xlSheet = xlBook.Sheets.Item("time")
sTask = xlSheet.Range("A3").value
sName = xlSheet.Range("B1").value
For Each tskT In ActiveProject.Tasks
If tskT.Name = sTask Then
MsgBox ("Found Task")
'For Each rsSingleRs In tskT.Resources

For Each rsSingleRs in tskT.Assignments

'If rsSingleRs.Name = sName Then

If rsSingleRs.ResourceName = sName Then

MsgBox ("Found Name")
'Set oTsvs = rsSingleRs.TimeScaleData("12/24/02", "12/24/02",
'pjResourceTimescaledActualWork, pjTimescaleDays)

Set oTsvs = rsSingleRs.TimeScaleData("12/24/02", _
"12/24/02",pjAssignmentTimescaledActualWork, _
pjTImescaleDays)

For Each oTsv In oTsvs
MsgBox (oTsv.value & "m")
'value = "240" value = 240
oTsv.value = value
Next oTsv
End If
Next rsSingleRs
End If
Next tskT

When i set oTsv.value = value i get an error. Am i at least on the right
track here?

Jeff,
OK, here's the answer. It had me baffled for about an hour and then I
figured it out. The error message is useless (as is often the case). The
fact is that at the resource level, the timescale data values are read
only. I should have figured that out sooner because in the Resource
Usage view, the data values cannot be changed at the resource level.
They can only be changed at the assignment level. If your code is
changed as annotated above (basically as Jan eluded to in his response),
it will run.

Hope this gets you going.
John

 

[JeffPoos]

Thanks guys for all of your help. I changed the code to parse through the assignment list instead and it works like a charm. Thanks again