Writing Timephased Data from MS Excel into MS Project 2003

[Carina Hira]

I'm working on a project for a senior manager who believes in the miraculous
programming capabilities of non-programmers. The goal of her presumptuous
project is for (initially just) myself and (now an expert) fellow programmer
to be able to take total work hours and total cost of a given employee from a
company generated SAP HR System spreadsheet and to write them into her
Microsoft Project 2003 file - with a snap of her beautiful and skinny
fingers! And just for her benefit. Urgh.

We are using VB.NET/Visual Studio.NET 2003 on Windows XP. We have imported
the necessary objects/references. We have the .NET framework setup. All our
patches are installed.

So far, we've been sucessfull in pulling the necessary values we need from
her messy excel spreadsheet and writing them into another/a clean excel
spreadsheet (EmployeeID, AssignmentID, TotalEmployeeHours, and
EmployeeTotalCost, PayPeriod). Our Unique Identifiers for writing the values
are EmployeeID and AssignmentID.

We've written code to convert hours from the excel spreadsheet to minutes
and to split that into the corresponding number of working days in MS Project
file for the given pay period. Then those values get assigned to the
appropriate dates. We are doing this based on Assignment. A little speedbump
was Project wanting to take values only in pairs : 15th/16th , 17th/18th etc
(Writing individual dates crashes the application). No problem. Worked around
that with a few mathematical calculations and variable comparisons. One more
little quirk we noticed while developing was that project would need to be
saved or refreshed before the values that were written in could be seen
accurately.

Now, when we try to write to Project and to save our file, we see that it
only writes the first couple of lines and then it gives us a
"System.Runtime.InteropServices.COMException" error (description: the
argument value is not valid).

We've been using the TimeScaleValue Collections/Object to do most of our
writing. I am quite sure that our logic is sound. At first we thought it
might be a memory error. But, after getting so close to completing it and
being so frustrated that I can't understand what the eff is going on any
more, I thought I'd reach out to get some comments/feedback.

If there is anyone who knows what might be going on or has the knowledge to
help, please write back. I would be ok with sending over the code and test
files for you to take a look at.

Thank you very much.
Sincerely,
Carina Hira

 

[JackD]

What is the code that is running when you get the error?
What are the values that you are passing at that time?

 

[Rod Gill]

Hi,

I hope the Assignment ID is from Project itself, otherwise you must work
with Task and Resource unique ids.

If you have a task covering 2 days and actual work is done before or after
those 2 days, you need to set the actual start date, then get the timephased
collection. For days after the current scheduled finish you need to do a
..add to add extra days to the task.

I've done this many times using VBA and the result works very well. Tip is
to manually enter actual hours into the resource usage view to see exactly
what steps you need to do.