Embeded Excel object

[SparkyUK]

How within VBA can I access and manipulate an Excel object embeded within the
MSProject file.
Would like to embed a spreadsheet to the project summary task (or Task(1)).
Use the rates within the spreadsheet to calculate the project cost from
resource usage.
Using Project2003 within a resource pool environment, with a large number of
projects. Each project has differing rates per resource, so can't use cost
tables (limited number of tables)
Thanks for any assistance available.

 

[John]

How within VBA can I access and manipulate an Excel object embeded within the
MSProject file.
Would like to embed a spreadsheet to the project summary task (or Task(1)).
Use the rates within the spreadsheet to calculate the project cost from
resource usage.
Using Project2003 within a resource pool environment, with a large number of
projects. Each project has differing rates per resource, so can't use cost
tables (limited number of tables)
Thanks for any assistance available.

SparkyUK,
That's an interesting one. As far as I know, there is no way to directly
access elements of an imbedded object, however you don't need to embed
anything. If for some reason you can't use the multiple resource rate
tables, (boy, you must have one whale of a rate structure), you can
access Excel data via VBA. It's "simply" a matter of establishing a
reference to an Excel application and then pulling the data directly
into Project for the rate calculations.

Fellow MVP, Rod Gill has an excellent book on Project VBA. Although most
of the examples in his book are directed at exporting Project data to
Excel, reversing the flow is straightforward. For more information about
Rod's book, go to http://www.projectvbabook.com.

Hope this helps.
John
Project MVP

 

[SparkyUK]

John,
Thanks for the reply.
I suspected as much, Project VBA interaction with embedded objects appears
to be very limited.
I was hoping to provide a self-contained solution, minimising the issues of
having links between files. Guess I am stuck with a rate spreadsheet solution.
Rob’s book has proved useful and I look forward to further Project VBA
publications from more authors.
I use a variant of Rob’s S-Curve solution and manually paste the s-curve
into the Gantt chart area, providing a status report that has Gantt-chart,
s-curve and other spreadsheet type information within one printout. It would
be great if I could automate this using VBA to insert objects and print.
Unfortunately, there is no method of deleting or updating the objects using
VBA.
Many thanks for your time.

 

[John]

John,
Thanks for the reply.
I suspected as much, Project VBA interaction with embedded objects appears
to be very limited.
I was hoping to provide a self-contained solution, minimising the issues of
having links between files. Guess I am stuck with a rate spreadsheet
solution.
Rob’s book has proved useful and I look forward to further Project VBA
publications from more authors.
I use a variant of Rob’s S-Curve solution and manually paste the s-curve
into the Gantt chart area, providing a status report that has Gantt-chart,
s-curve and other spreadsheet type information within one printout. It would
be great if I could automate this using VBA to insert objects and print.
Unfortunately, there is no method of deleting or updating the objects using
VBA.
Many thanks for your time.

SparkyUK,
You're welcome and thanks for the feedback. Just for reference, he's Rod
Gill, not Rob Gill.

To clarify a couple of things. You do NOT need links between Project and
Excel to do what you want (i.e. applying multiple rates to resources).
In fact, the whole point of using VBA is to avoid the necessity for
links. However, it sounds like you are after a more graphical rich
presentation for Project and for that you would need to embed an Excel
graphic. Maybe what you should do is to export all the data you need to
Excel and then use Excel's vastly superior graphic capability to produce
the report you want.

John
Project MVp