Forum Discussion
Project Import from Excel - Assignments with Time Phased Work
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: Assu...
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
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
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)
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)