Creating monthly Actuals reports for all active projects.

M

Matt Dunphy

I would like to preface the following post by saying that I realize
Project is not the ideal solution for invoicing, but we're kind of
using it like that, and I'm going to have to find a better way of doing
what I'm doing this week.

At this point, to get monthly actuals for each project, I'm opening
each Project in Professional, analyzing timescaled data to Excel,
removing tasks with zero hours, and printing the tasks that have
actuals entered, i.e.

Analyze -> Entire Project -> Actual Work ->
From 11/01/04 to 11/30/04, Units: Months -> No Graph Please -> Finish.

I realize that in order to do this efficiently, I'm going to have to
automate the process, because most of the stuff I'm clicking or
deleting could be handled by a pretty straightforward script. It would
be greatly helpful though if I had a decent starting point... even if
it's just something that gives me an idea of how to duplicate the above
process in VBA. I would actually need something broader, basically
scanning each project on the server for actuals amongst the timescaled
data.

The ideal end result would be a worksheet in Excel for each project,
showing actual hours only in those tasks where time has been entered
between a start and end date, with a "Monthly Total" of that project's
actuals.
Any help would be greatly appreciated, thanks!

--Matt Dunphy
 
J

John

Matt Dunphy said:
I would like to preface the following post by saying that I realize
Project is not the ideal solution for invoicing, but we're kind of
using it like that, and I'm going to have to find a better way of doing
what I'm doing this week.

At this point, to get monthly actuals for each project, I'm opening
each Project in Professional, analyzing timescaled data to Excel,
removing tasks with zero hours, and printing the tasks that have
actuals entered, i.e.

Analyze -> Entire Project -> Actual Work ->

I realize that in order to do this efficiently, I'm going to have to
automate the process, because most of the stuff I'm clicking or
deleting could be handled by a pretty straightforward script. It would
be greatly helpful though if I had a decent starting point... even if
it's just something that gives me an idea of how to duplicate the above
process in VBA. I would actually need something broader, basically
scanning each project on the server for actuals amongst the timescaled
data.

The ideal end result would be a worksheet in Excel for each project,
showing actual hours only in those tasks where time has been entered
between a start and end date, with a "Monthly Total" of that project's
actuals.
Any help would be greatly appreciated, thanks!

--Matt Dunphy

Matt,
The process could definitely be done with a VBA macro. Are you looking
to write the code yourself or are you looking for someone to write it
form you? If you have some VBA experience the pseudo code could probably
be something like this:
1. Set up a loop to open and examine each project
2. For each project establish a filter for only the period (i.e. month)
of interest. This could be a user input at the beginning of the macro. A
filter isn't absolutely necessary but it might make data gathering
easier.
3. If you only need to capture the data and don't need to perform any
manipulation on it, open Excel for output and keep it in the background.
I would at this time also pre-format the Excel worksheet. If you do need
to operate on the basic data, I would open Excel after I gathered the
data from Project.
3. Use the TimeScaleData Method to gather the desired data
4. As each data value is gathered, write it into the pre-formatted Excel
Worksheet.
5. When all data is gathered and written to Excel, initiate a print of
the Worksheet.

The details may vary a bit depending on your file structure and the
format needed for the Excel Worksheet.

Hope this helps.
John
Project MVP
 
L

Leviathant

John said:
Matt,
The process could definitely be done with a VBA macro. Are you looking
to write the code yourself or are you looking for someone to write it
form you? If you have some VBA experience the pseudo code could probably
be something like this:

***snip***

Nah, I wasn't necessarily looking to have someone write the macro for
me, but was seeing if anyone had already written something like this,
because I like to avoid reinventing the wheel. That and I was looking
for certain key command, which I believe you posted in point three...
3. Use the TimeScaleData Method to gather the desired data

Now my signal to noise ratio while perusing the SDK is fantastic, and
I can get right to what I need. I think. And if not, well, there's
always the "Extracting Timephased Data from the Microsoft Office
Project 2003 Database" article. But I was hoping to avoid that.

Thanks so much!
 
J

John

Nah, I wasn't necessarily looking to have someone write the macro for
me, but was seeing if anyone had already written something like this,
because I like to avoid reinventing the wheel. That and I was looking
for certain key command, which I believe you posted in point three...


Now my signal to noise ratio while perusing the SDK is fantastic, and
I can get right to what I need. I think. And if not, well, there's
always the "Extracting Timephased Data from the Microsoft Office
Project 2003 Database" article. But I was hoping to avoid that.

Thanks so much!

Matt,
You're welcome. Go for it.

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top