Time-phased data on per-project basis?

[Carl]

I work with a single schedule file that has multiple projects in it. Each
project has a unique identifier in the "Project" column. I have used this
with autofiltering to select all the tasks for a project, then export
time-phased data to Excel for further analysis. I know I could go through on
a per-project basis and repeat the steps to get time-phased data for each
project, but I would like to know if there's a more streamlined way to do
this? The ideal output is a single Excel page with time-phased "work" and
"actual work" totals provided on a weekly basis.

-- Carl

 

[Rod Gill]

To get only some tasks from a project in one Excel sheet and other tasks
exported to another sheet requires a custom Project VBA macro (which I
presume is why you posted in this group!). This is relatively
straightforward, but needs careful design to make it easier. Most of the
code you need is actually in my book. To get you started:

'Link to Excel and create new Workbook
'Loop thru all tasks
'For all tasks for current project
'Create new sheet and headers for this project
'Export Timephased data
'End of project
'next task

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Carl]

Rod,

Thanks for the response, and how convenient: a copy of your book is headed
my way!

Your pseudocode prompts two questions:

1) How do I set the "current project"? Is there a way to loop over the list
I see when I click on the autofilter drop-down button of the "Project" column?

2) Can each project's exported data be appended so we only have a single
sheet summary to review?

We're trying to answer the "how many hours did we spend on each project this
week" question. I'm okay with programming to get that answer, but I don't
want to reinvent the wheel if there's already a way to get it.

-- Carl

 

[Rod Gill]

I assumed all Tasks for each project are grouped together, if not, sort by
the project column first. Then store the first project name in a string
variable and treat all tasks as belonging to this project until the project
name changes, then create a new sheet.

ActiveProject.Tasks returns all tasks in the active project.

Enjoy the book!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Carl]

Rod,

Thanks, I'm sure I'll be able to come up with something that works for me.

The book was waiting for me when I got home tonight. Just flipping through
it answered a few questions. Looks like a reference that will actually get
used!

-- Carl

 

[Rod Gill]

Excellent, thank you!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com