MS Project to Excel export

[rob walter]

Hi,

Basically, I'm trying to export the data in have in a
particular MS Project View into an Excel worksheet. (Both
products are at the 2000 level). I need to do this
regularly and reliably !!!. Details are as follows:-


In Project I have the "Task Usage" view setup with the
timescale (along the top) at a granularity of "weeks".
Down the side I have the names of Rolled-up tasks
subdivided into individual tasks, which are in turn
subdivided into names of people who will work against the
task. In the adjoining column I have defined and hand-
populated a custom field called "booking code".
The "body" of the table shows how many hours the person
(or task) is planned to consume in the appropriate week.

I want to get this whole view into Excel pretty much "as-
is" so that I can combine it with data I obtain from other
sources, and subsequently process with various macros
(macroes?). I've tried 3 approaches :-


EXPORT
The Project "SaveAs" option seems to offer some promise
but none of the standard "Export Mappings" offered appears
to preserve the breakdown of the data into weekly totals .
Even the custom Export Mappings do not appear to have the
configurability to give me what I want.

REPORT GENERATION
I've also played with the Report generation capability
within Project. I have experimented with custom report
formats of type "crosstab". I can get a report that looks
almost as I require except for two problems :-
1) I cannot get BOTH the task name and booking code
columns on the "side headings" of the report
2) The Reports are directed to a printer...how do I (with
minimum hassle, remember !!) intercept and get into Excel
instead?


COPY/PASTE
Simply copy/pasting cells isn't particularly satisfactory -
it doesn't copy the whole "view". The best I have been
able to achieve is to copy/paste it in two chunks; the
first to copy/paste the task names and booking codes
columns, and the second to copy/paste the weekly
breakdowns. Even this leaves out the date headings at the
top...so I have to add them into Excel afterwards by
hand....So, although this method "works" its extremely
clunky and is rather too error prone for my requirements

Any ideas/advice gratefully received.


Many Thanks
Rob

 

[John]

Rob,
It looks like the one approach you haven't tried is using the "Analyze
timescaled data in Excel" macro which is available on the "Analysis"
toolbar. If that toolbar is not visible, just right click anywhere in a
blank area of the Project toolbar and select it. The noted macro will
transfer timescaled data to Excel based on various selection criteria
from a user interface.

If for some reason the built-in macro is not available on your PC, you
can download it from the MS Project web page at:
http://www.microsoft.com/downloads

If the built-in macro does not quite do what you want, you can always
write, or have written, a custom macro to transfer exactly what you want
to Excel. Myself, or others in this newsgroup can help you with that.

John

 

[Sandra Davison]

Thanks Rob ... but I've found that option. When I click
on the toolbar button, nothing happens. Is there
something in the setup that would prevent this working?

 

[Jack D.]

Thanks Rob ... but I've found that option. When I click
on the toolbar button, nothing happens. Is there
something in the setup that would prevent this working?

Read the project help (search for analyze timescaled data) to troubleshoot
what is happening.
You might need to do a reinstall.

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently. For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP


+++++++++++++++++++

 

[rob]

John,

BRILLIANT !!! I've experimented with your suggestion and
it looks like it can be made to do just what I need .

Many Thanks
Rob

 

[Sandra Davison]

Jack - I've done a reinstall (of the Wizard), and shut
down the laptop and still it's not working. Any other
suggestions ... my client really needs the resource
stacked histogram yesterday!

 

[John]

Sandra,
If I understand you correctly, you do get the toolbar with the button
for the "analyze timescaled data in Excel" macro (add-in), but the code
behind the macro itself does not execute - correct? There should be no
other magic to making it run and I don't honestly know why it doesn't
work. For lack of other suggestions, why not try downloading the code
itself directly from the MS web page as I suggested in yesterday's post
and see it that will work. Otherwise, maybe someone else has a trick you
can try.

However, if it is just a histogram of peak units for all resources on a
timescale basis, you can get that directly from Project by displaying a
Resource Graph in the lower half of a split screen. I haven't used that
feature myself so I'll have to refer you to the help file for more
information.

John

 

[Rob Schneider]

Sandra,

I had that problem with my initial install of Project 2002 where I was
re-using an old global.mpt file. The older file has "roots" in Project
98 and before.

As I recall, I deleted the old global.mpt file, then un-installed
Project, then re-installed. Then the Excel time series wizrd was
correctly in place.

I then also then manually moved the stuff from the older global.mpt file
into the newone.