|
|
|
date: Wed, 1 Oct 2008 12:17:02 -0700,
group: microsoft.public.project.developer
back
Re: Time-phased data on per-project basis?
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" wrote in message
news:7473966D-2CB7-4001-9DFE-4DBE782593D3@microsoft.com...
> 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" wrote:
>
>> 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" wrote in message
>> news:489DB234-3AA3-408B-A8F8-74B6BC890385@microsoft.com...
>> >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
>>
>>
date: Thu, 2 Oct 2008 17:42:46 +1300
author: Rod Gill rodATproject-systemsDOTcoDOTnz
Re: Time-phased data on per-project basis?
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" wrote:
> 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" wrote in message
> news:7473966D-2CB7-4001-9DFE-4DBE782593D3@microsoft.com...
> > 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" wrote:
> >
> >> 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" wrote in message
> >> news:489DB234-3AA3-408B-A8F8-74B6BC890385@microsoft.com...
> >> >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
> >>
> >>
>
>
date: Thu, 2 Oct 2008 18:25:02 -0700
author: Carl
Re: Time-phased data on per-project basis?
Excellent, thank you!
--
Rod Gill
Microsoft MVP for Project
Author of the only book on Project VBA, see:
http://www.projectvbabook.com
"Carl" wrote in message
news:D4C25CB7-48A6-4B1C-B9E9-C454E8CAB62C@microsoft.com...
> 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" wrote:
>
>> 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" wrote in message
>> news:7473966D-2CB7-4001-9DFE-4DBE782593D3@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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" wrote in message
>> >> news:489DB234-3AA3-408B-A8F8-74B6BC890385@microsoft.com...
>> >> >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
>> >>
>> >>
>>
>>
date: Sat, 4 Oct 2008 08:56:42 +1300
author: Rod Gill rodATproject-systemsDOTcoDOTnz
|
|
