Exporting Tasks and Resources from MS Project 2007 to MS Excel

[shamailauk]

Hi there,

I need to export the tasks and associated resource information to Excel
but I am having trouble modifying the following Macro (I'm not a
coder!). Essentially what I would like is to include the Resource Group
and % Complete fields in the export.

Here is the code I was able to find, I was able to modify some of it to
include start and end dates. I'd appreciate any help!

Thanks,
S

-------------------------------------

'This module contains macros which will export
'tasks to excel and keep the task hierarchy.
'modify as necessary to include other task information

'Copyright Jack Dahlgren, Feb 2002

Option Explicit

Dim xlRow As Excel.Range
Dim xlCol As Excel.Range

Sub TaskHierarchy()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Proj As Project
Dim t As Task
Dim Asgn As Assignment
Dim ColumnCount as Integer
Dim Columns as Integer
Dim Tcount As Integer

Set xlApp = New Excel.Application
xlApp.Visible = True
AppActivate "Microsoft Excel"

Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = ActiveProject.Name

'count columns needed
ColumnCount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.OutlineLevel > ColumnCount Then
ColumnCount = t.OutlineLevel
End If
End If
Next t

'Set Range to write to first cell
Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & ActiveProject.Name
dwn 1
xlRow = "OutlineLevel"
dwn 1

'label Columns
For Columns = 1 To (ColumnCount + 1)
Set xlCol = xlRow.Offset(0, Columns - 1)
xlCol = Columns - 1
Next Columns
rgt 2
xlCol = "Resource Name"
rgt 1
xlCol = "Start Date"
rgt 1
xlCol = "Finish Date"

Tcount = 0
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
dwn 1
Set xlCol = xlRow.Offset(0, t.OutlineLevel)
xlCol = t.Name
If t.Summary Then
xlCol.Font.Bold = True
End If
For Each Asgn In t.Assignments
dwn 1
Set xlCol = xlRow.Offset(0, Columns)
xlCol = Asgn.ResourceName
rgt 1
xlCol = t.Start ' capture task start
rgt 1
xlCol = t.Finish ' capture task finish
Next Asgn
Tcount = Tcount + 1
End If
Next t
AppActivate "Microsoft Project"

MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
End Sub
Sub dwn(i As Integer)
Set xlRow = xlRow.Offset(i, 0)
End Sub

Sub rgt(i As Integer)
Set xlCol = xlCol.Offset(0, i)
End Sub

--
shamailauk
------------------------------------------------------------------------
shamailauk's Profile: http://forums.techarena.in/members/168766.htm
View this thread: http://forums.techarena.in/microsoft-project/1287460.htm

http://forums.techarena.in

[Rob Schneider]

This is a relatively complex VBA macro and the odds of getting great
help increases if you re-post on the newsgroup for developers:
microsoft.public.project.developer.

That being said, I'm wondering if you would find it easier to use the
built-in capability of Project to do this. See Menu: File/SaveAs ...
and pick Microsoft Excel XLS file. You will be presented with a wizard
which will work you through the process of picking the tasks, resource,
and assignment data that you wish to export. Here is a "help" button on
that wizard dialogue box which provides some guidance. You'll end up
creating a MAP file which you can then re-use for future exports.

--rms

www.rmschneider.com

> MsgBox ("Macro Complete with "& Tcount& " Tasks Written")

[shamailauk]

Hi Rob,

I have tried creating a new map but unfortunately you lose all the text
formatting that way. I'll try posting to the developers forum you
suggested.

Thanks,
S

[Rob Schneider]

ok. format in Excel once data gets there?
(avoids all the VBA complexity)

--rms

www.rmschneider.com