Export MS Project 2007 to Excel

[Manik]

Can anyone tell me a way to export Microsoft Project 2007 to Excel and then retain the formatting? The tasks that I am having all show as level 1 but not sub tasks, etc? Thanks for any information.

[Janos™]

You can try to test a macro that was created for Exporting hierarchy to Excel macro, that is given below:

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 = "work"
rgt 1
xlCol = "actual work"
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 = (Asgn.Work / 480) & " Days"
            rgt 1
            xlCol = (Asgn.ActualWork / 480) & " Days"
        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

You can also try to export the Outline Level field to Excel and then apply the indentation in Excel.

[Manik]

Could you please tell me a way to apply the indentation in Excel. Thank you.

[jigu902003]

You can try to export the Outline level field to Excel along with the rest of the data and then you can use the autofiltering to show only tasks with the selected tasks. After that you can use the Indent buton in the Alignment Group on the home tab to Indent for each outline level. For instance, outline Level 2, I would indent once -- outline level 3, I would indent twice.

[PeterAGASSI]

You can also use something called as an EasyProjectPlan which is a useful and reliable application that will help standardize the process in which team members define and inform your homework. EasyProjectPlan is designed for distribution to project team members so they can easily input their tasks, dates and status using a standardized interface and it is easy to use. EasyProjectPlan templates can be imported into Microsoft Project to create instant Gantt charts and can synchronize data between the two programs.