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 and retain text formatting.

I came across JackD's site and was able to modify some of it to include
start and end dates but I'm not able to include the additional fields I need.

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