Project Import from Excel - Assignments with Time Phased Work

[wjls20854]

I'm trying to do something very simple, but I can't find "the answer".  Asking for help in finding it.

Goal: Populate time phased Work into MS-Project assignments.

Setup:

In MS-Project: Assume that my MS-Project file has a number of simple tasks, say 100 tasks with their durations and dependencies.  Let's assume no summary level tasks to keep it simple. 

In Excel: I have a table with about 1000 rows, in which I have the UIDs of the tasks and the resources assigned to each task in columns at the left hand side.  Then to the right I have 52 columns with the hours that each resource is going to spend on each task spread out by weeks.

I want to pull that assignment data (resources and time phased work) into MS-Project.  Ideally I'd like to do that using a simple import map, but if I have to use VBA I'm ok with that, but hoping someone has already written the code.  I've looked for this particular problem but I don't see it written up anywhere.  Can someone point me to a post or provide a link to instructions for pulling in time-phased data at the assignment level like this?

Thanks

[#2]

wjls20854,
Well, you won't be able to transfer the data with a simple import map, VBA is required, unless of course you want to copy and paste the data week by week.

Do I already have VBA code to do what you want? No, most of my macros go the other way (i.e. Project data, static and timescaled data to Excel). I have a few macros that transfer data from Excel to Project but importing Excel data into Project is highly dependent on the Excel Workbook structure since Excel has an open format. It sounds like you already have a well defined Excel Workbook format, so you're off to a good start.

The macro code to do what you want is not difficult but it does require some experience. Do you have any VBA experience?

John

[#3]

Thanks John.

I was hoping to avoid having to code in VBA because it tends to take me a long time to debug and get it working...and I'm rusty. [I was really hoping that the MS-Project import map functionality could be used for time phased data export. It sounds like you are saying that it does not.]

I do have some experience with VBA and in particular many years ago I wrote a macro that exported Assignment level details from MS-Project to Excel. I can try to get the shell of that going again. Can you help with the core code, ie the loops and what I would need to copy into, and the most effective way to copy? After opening up a connection between MS-Project and Excel, I presume I would first copy the data from Excel into an Array, and then loop through the appropriate tasks and assignments copying the data into the MS-Project data structure.

Any help with the code would be appreciated.

Thanks

[#4]

wjls20854,
Over the years I've used three methods for transferring data from Project to Excel. 1) Gather all the Project data into arrays, open an instance of Excel, dump the arrays into the Worksheet, format as necessary and close. 2) Open an instance of Excel, pre-format the Worksheet, on the fly read and write Project data into the Worksheet, final format and close. 3) Write Project data to a text file, open the text file in Excel and format as necessary.

I always felt the first method was the fastest since it's operating on only one application at a time but it takes more time and effort to set up and keep track of all the indexing for the array data. However, of late my preferred method is number 2, no arrays required although keeping track of indexing the data can still be challenging. I only used method 3 once or twice and that was many years ago.

Going the opposite way is no different. The first choice is whether to have the code reside in the Excel Workbook or in the Project file. If it's a new Project file each time, it's easier to have the macro in Excel but if the Project file already exists and the main focus is to update with new data from Excel, I'd have the code resident in Project. What's nice about having a macro in Project is you can keep it in your Global.

There are two ways to write a macro, using what I call foreground processing (what you get when you record a macro) or background processing. Foreground processing operates on the active view (selected items) and therefore runs slower. Background processing operates directly on objects in the underlying Project or Excel database, independent of view and therefore runs faster. I always use background processing except in the few cases where the only method available is by selection (e.g. font formatting in Project).

I'd be glad to help you out. It might be easier if we work one-on-one via e-mail. My address is below. I will ask some questions.

John
jmacprojataticlouddotdotcom
(remove obvious redundancies)