Editing MS Project File from Excel VBA

[andrew.littlefield]

I posted this in another forum, but because there seems to be no other
information on the topic i felt it would be beneficial to ask here as
well. (link to original post: http://www.excelforum.com/showthread.php?t=626117)

What i'm attempting to do is edit a Microsoft Project file from an
Excel VB Macro. We have a program that populates most of the necessary
fields of Project file automatically, but other critical fields
(durations) will need to be entered manually. To expedite the process,
i'm attempting to create an Excel template for the responsible parties
to update and would like the macro to then update the Project file.

I realize there are easier methods other than macros to do this, but
as there are 30,000+ lines, many of which are repeated, a macro would
be the most efficient.

The only code i have been able to find that even links the two
different MS programs is this:


Set ProjApp = CreateObject("MSProject.Application")
With ProjApp
.Visible = True
' xxxxxx
.Quit
End With
Set ProjApp = Nothing

While it does somewhat link the two programs, i'm experiencing
problems right off the bat and am fairly certain that is not the best
way of doing what i'm needing.

Any advice, examples, or references would be much appreciated.

Thanks in advance,

 

[KC Cheung]

Why we cannot use simple cut and paste please?

 

[the dude]

Cut and paste would be easiest, but incredibly time consuming. As i
said, (ideally) the responsible parties will update a template
containing tasks relevant to them. The macro will then locate those
tasks dispersed throughout the 30,000+ lines and update accordingly.
Because this could/probably will be done numerous times throughout the
duration of the project, it would be much more efficient to have a
macro take care of the searching and locating, then copying/pasting
automatically.

I've been pointed to references by others and will post them here
after i've had a chance to do some research, but if anybody out there
has any ideas please let me know.

Thanks again.

 

[KC Cheung]

We are assuming you have Activities, Duration etc in Excel spreadsheet.
We always do this.
Copy the whole column of 30,000+ durations;
Switch to Project;
Click top cell in duration column;
Paste;