Importing actual work data, and setting assignment notes using VBA

Question

Hi folks,

I'm working on a solution for a problem that I imagine many of us have - actual work data, i.e. time sheets, are not maintained in Project and have to be imported. What a royal pain. Our company has a legacy application used for recording time sheet, and this dinosaur will not be leaving any time soon. I have at times manually inputted data into my Project in the past, but I'm almost finished with some VBA code that I hope will be able to to do this for me in the future.

Each timesheet recordset that I import luckily already has a WBS code defined, so I use that to find the associated task in my Project, access the assignment associated with that particular task and resource, and then define the Actual Work using the timeScaleData method.

Along with the basic data just described, I want to add the unique identifier coming from our dinosaur timesheet application to the Assignment.Notes; the operation itself is successful, but I can't find any view that will allow me to access this field.

Any ideas?

Pug McThug

Answer 1

Pug,

Well, your code runs fine for me using Project 2010 with SP1.

I set up a simple file with 3 tasks and two resources per task. Rather than using a filter on the WBS field, I simply selected a single task in the Gantt Chart view. I used today's date for MyDate and 60 for the Minutes variable, the former declared as Date type and the second as Single type. I ran your code and then looked at the Resource Usage view which included the Notes field as a column in the view (since the note text is short this is more convenient than having to go to Assignment/Information to see the notes text). The test string appeared in the Notes field of both assignments for the task I had selected in the Gantt Chart view.

If the above simple example doesn't work for you, then you could try an even simpler test. Create a simple file similar to the above. In the VB Editor window display the Immediate Window. Execute the following line of code in the Immediate Window:

activeproject.Tasks(1).Assignments(1).Notes="hello"

Then check the Notes field for the first assignment on the first task. Is the text there?

John

Answer 2

Pug,

You're velkommen and tanks for da feedback.

Hmmm, pilot error you say. It seems I've done that a few times myself.

If this answered your question, please mark your post as answered.

John

Answer 3

John,

thanks for the tip, it lead me to the problem, which can only be described as <ahem> "Operator Headspace and Timing". The code execution was jumping out of a loop prior to defining the .Notes field.

Tenk yu veddy mutsch.

Pug

Answer 4

Hi John,

thanks for the quick response - that's exactly what I was trying, but to no avail.

I could of course not be writing the data properly, here's a snippet:

Prior to this code snippet running, I have implemented a filter using the WBS number, so ActiveSelection.Tasks returns only one task. The variables in Bold/Italic are already defined. The underlined statement is where I'm (trying) to out text in to the assignment notes.

For Each task In ActiveSelection.Tasks

For Each assignment In task.Assignments

Set tsvs = assignment.TimeScaleData(Startdate:=MyDate, _

EndDate:=MyDate, Type:=pjAssignmentTimescaledActualWork, _

TimeScaleUnit:=pjTimescaleDays, Count:=1)

For Each tsv In tsvs

tsv.Value = Minutes

Next tsv

assignment.Notes = "This is a test"

Next

Ideas?

Pug

Answer 5

Pug,

Okay, two ways to get there. First be advised that you will need to have one of the Usage views active (i.e. Resource or Task) and then select an assignment row.

Method 1 - Double click on the assignment row

Method 2 - On the ribbon go to Format/Assignment group/Information

Hope this helps.

John