Excel links w/ VBA

[Smmiles]

hello
I am using Excel reports that are linked to multi project files. I am setting these up manually, ie, special-pasting, as text, project data into the Excel worksheets. Rather than "paste special" project data by each field, is there a better way? For example, set a region in Excel to accept the pasted data as "linked". IE, format the region and then paste away? This is the fastest strategy I've come up w/. Is there a faster strategy than this? (Exporting maps isn't the right solution as I am looking at select fields from several plans, all reported in single worksheets.

'Appreciate your insights and brilliance on this topic.

 

[JackD]

Those sorts of pasted links are fragile and can be difficult to deal with
when they break (and sooner or later they usually do)

You could try and make the maps work for you by creating a shell project
(basically a temporary master project which contains all of your project
files) setting a flag for the tasks you want to export (use flag1-10)
creating a filter on that flag and then setting up a map which exports using
that filter.

Another alternative is to write some VBA code which opens the files,
extracts the relevant data and then writes to excel.

I have an example of how to write from project to excel at my website:
http://masamiki.com/project/macros.htm

You could use that as a start.

-Jack

hello:
I am using Excel reports that are linked to multi project files. I am

setting these up manually, ie, special-pasting, as text, project data into
the Excel worksheets. Rather than "paste special" project data by each
field, is there a better way? For example, set a region in Excel to accept
the pasted data as "linked". IE, format the region and then paste away?
This is the fastest strategy I've come up w/. Is there a faster strategy
than this? (Exporting maps isn't the right solution as I am looking at
select fields from several plans, all reported in single worksheets.)

 

[Smmiles]

THANKS~ Also been dealing with the fragility of the links. Would have otherwise been a great little technique.

 

[Smmiles]

Hello Jack:
Thanks for your export module--I've modified a few times w/ great results. I've also been using your "Turn Bars Green". Would like to turn the font green for these as well. My modification looks for tasks that are 100% complete, adds a comment to a custom field, and then turns the gantt bars green. I'm stuck on how to turn the font for these green too. I'd rather NOT have to first select the tasks, nor do I want the entire sheet to be turned green. Any ideas?

 

[JackD]

Hello Jack:
Thanks for your export module--I've modified a few times w/ great results.

I've also been using your "Turn Bars Green". Would like to turn the font
green for these as well. My modification looks for tasks that are 100%
complete, adds a comment to a custom field, and then turns the gantt bars
green. I'm stuck on how to turn the font for these green too. I'd rather
NOT have to first select the tasks, nor do I want the entire sheet to be
turned green. Any ideas?

One of the options in the format/text styles menu is to change either
"marked" or "highlighted" text styles.
One of the options for a filter is to set it to be a "highlight" filter.
So to start I'd fire up the macro recorder.
Create a filter which looks for tasks that are 100% complete.
Set the filter to be a "highlight filter"
Apply the filter.
Then go to format menu /text styles and set the "highlight" text item to be
the color and format you want.
Then take a look at the code. A little clean up of the code to remove
anything extra should be more than enough.

-Jack