Exporting to excel

[Niki]

Hi guys

I would like to export data from several MSP documents into the same excel
spreadsheet. Making the data append to the bottom each time and not
overwriting the data that is already there.

Is there some VB script that I can use to do this task?

Niki

 

[John]

Hi guys

I would like to export data from several MSP documents into the same excel
spreadsheet. Making the data append to the bottom each time and not
overwriting the data that is already there.

Is there some VB script that I can use to do this task?

Niki

Niki,
You didn't mention whether you just need static Project data or
timescaled Project data. Depending on exactly what you need, you may be
able to use a series of export maps (no VBA required). Whether the data
is dumped into one or more Pivot Tables or simply exported to individual
Worksheets which are then combined is probably a matter of preference.
Export maps are discussed in the Project Help file.

If the data you want to export is timescaled, you can use the "analyze
timescaled data in Excel" add-in/utility. The exact data exported is a
little limited and some manipulation of the exported data will probably
be necessary but at least you will have the basics.

My preferred method regardless of which type of data is exported is to
create a custom VBA macro. I have some VBA code samples and you can also
find some on my fellow MVP's, Jack Dahlgren", website. Since each user
has their own unique requirements, sample code would have to be modified
to get exactly what you want. You can access Jack's site at:
http://masamiki.com/project/macros.htm
or you can write me direct.

Hope this helps.
John
Project MVP

 

[Niki]

Hi John

The data I want to export is nothing fancy, just a few text, date and number
columns. It's not time scaled, just static project data. I need it exported
to a plain worksheet, no pivot table.

I had a look on Jack's site and on the project help but I'm probably looking
in the wrong place or not searching for the correct words??

I already use a macro to export this data from each Project document to an
excel worksheet so I just need to add something to the start of this to tell
it to add onto the bottom of the data instead of replacing the whole excel
document.

The script I use saves the spreadsheet into the project file's current
location and replaces the ".xls" with "_Milestones.xls" so I can distinguish
it from other exports:

FileSaveAs Name:=Left$(ActiveProject.FullName, Len(ActiveProject.FullName) -
4) & "_Milestones.xls", FormatID:="MSProject.XLS5", map:="Forecasting -
externals/milestones/issues"

Am I making sense yet?

Niki