Convert to Excel Without Reformatting

[REDesigner09]

Hi,

Is there a way to convert my MS Project 2007 WBS tasks to Excel, without having it get reformatted?

Ideally, I would like a WYSIWYG formatting from MS Project to Excel 2007, without minimal to no formatting corruption.

Is this possible & if so, how?

Thanks

 

[SueK]

I'm only skimmed this thread, so forgive me if this answer is too simplistic, but this sounds like an issue I've played with recently.

If you save as an Excel file, rather then just copy paste to Excel, you'll get a field "Outline level". In Excel, it's very fast to filter on Outline level to adjust the format so that you can get something similar to MS Project. I've indented each level twice the prior level and gotten nice results, highest levels, I bold or italicize.

It takes only a quick minute, once you get the routine down. Select all but level 1, indent twice, remove level 2 from the filter, indent twice, etc. You don't even have to reselect the format area, just adjust the filter.

As for the dates, you should be able to just format out the time. It's still there to calcualte with, but less visually messy.

Once you've set up once, you should be able to copy and paste in updates, as long as the number of lines hasn't changed. Otherwise, perhaps you paste your formulas to a new extract.

 

[PSPM]

Hi SueK,

I tried doing this & being asked a bunch of MS Project fields. If it did convert, I must've missed the option to give a directory to save to. Now, I have no idea where the file saved.

Selecting these fields to convert seems a bit cumbersome & more than 5 minutes but I should try a few more times to see how well this works.

Thanks

 

[ierland]

If I see your example you want to see the date without the time./ Indeed this can be achieved with a formula. I would anyhow not use DATEVALUE but rather DATE. This as DATEVALUE is language dependent and requires a lot more extra gimmicks. Why not use INT or FIX and the appropriate formatting. Actually you could do that already on the original fields.
Just stripping the number of the duration field would be quite easy by searching for the first space, using the result in a LEFT function and embracing that with VALUE.

There is, however, probably another way to achieve what you want by using some custom fields in MS Project. Take for instance a number field and just insert as formula [Duration]. This will give the duration in minutes. With standard 8 hours per day and five days per work week you would have to use [duration]/480 and [duration]/2400. There are functions to take into account arbitrary values for hours per days and days per week but likely you do not need these. The advantage is that all extra free days on the calendars would be taken into account, even if you had used a special calendar for certain tasks. With your Excel formulas this would not be easy if impossible.
What else do you need that you can not achieve within MS Project? This is also not (yet) clear to me.

Why an easy solution if you can make it complicated?
Greetings from the Netherlands

 

[chamdan]

You could use this macro listed below.

Option Explicit
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range
Dim NbrTask As Integer

Sub ExtractMSP()
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
Dim TRowCount   As Integer
Dim ActP As String
Dim KT  As String
'=========== Destination Path Name =================
Dim dPath  As String
dPath = "D:\ \"   ' set the pathname here
Dim wb              As Workbook
Dim NewShtName      As String
Dim OrgFileName     As String
Dim Counter As Integer: Counter = 1
Dim PctDone As Single
'
Entry_View
'

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

OrgFileName = dPath & "ScheduleTemplate.xlsm"
Workbooks.Open FileName:=OrgFileName
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Sheets("Task_Table1").Select
Cells.Select
ActiveSheet.Paste
Range("A1").Select
ColumnCount = 0
TRowCount = 1
For Each T In ActiveProject.Tasks
Application.ScreenUpdating = False

    If Not T Is Nothing Then
        If T.OutlineLevel > ColumnCount Then
            ColumnCount = T.OutlineLevel
        End If
    End If
Next T
ColumnCount = ColumnCount - 1

'Set Range to write to first cell

Set xlRow = xlApp.ActiveCell
xlRow = "Filename: " & Left(ActiveProject.Name, Len(ActiveProject.Name))
TRowCount = TRowCount + 1
dwn 1
xlRow = "OutlineLevel"
TRowCount = TRowCount + 1
dwn 1

'label Columns
For Columns = 1 To (ColumnCount + 1)
    Set xlCol = xlRow.Offset(0, Columns - 1)
    xlCol = Columns - 1
Next Columns
'
' rgt is the column skip to the right
'
rgt 2
xlCol = "WBS"
rgt 1
xlCol = "Task ID"
rgt 1
xlCol = "% Complete"
rgt 1
xlCol = "Duration"
rgt 1
xlCol = "Start"
rgt 1
xlCol = "Finish"
rgt 1
xlCol = "Actual Start"
rgt 1
xlCol = "Actual Finish"
rgt 1
xlCol = "Predecessor"
rgt 1
xlCol = "Successor"
rgt 1
xlCol = "Critical"
rgt 1
xlCol = "Milestone"
rgt 1
xlCol = "Resource Name"
rgt 1
xlCol = "Resource Group"
Tcount = 0

For Each T In ActiveProject.Tasks
    If Not T Is Nothing Then
        Tcount = Tcount + 1
    TRowCount = TRowCount + 1
        dwn 1
        Set xlCol = xlRow.Offset(0, T.OutlineLevel)
        xlCol = T.Name
            If T.Summary Then
                xlCol.Font.Bold = True
'
' Added to print the data that belongs to the Summary Tasks
'
                Set xlCol = xlRow.Offset(0, Columns)
                xlCol = T.WBS
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ID
                xlCol.Font.Bold = True
                rgt 1
                xlCol = (T.PercentComplete / 100)
                xlCol.Font.Bold = True
                rgt 1
                xlCol = Int(T.Duration / 480) & " d"
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Start
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Finish
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ActualStart
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ActualFinish
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Predecessors
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Successors
                xlCol.Font.Bold = True
                rgt 1
                If T.Critical = True Then
                    xlCol = "Yes"
                    xlCol.Font.Bold = True
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = True
                End If
                rgt 1
                If T.Milestone = True Then
                    xlCol = "Yes"
                    xlCol.Font.Bold = True
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = True
                End If
                rgt 1
                xlCol = T.ResourceNames
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ResourceGroup
                xlCol.Font.Bold = True
                rgt 1
            End If
        For Each Asgn In T.Assignments
            Set xlCol = xlRow.Offset(0, Columns)
            xlCol = T.WBS
            xlCol.Font.Bold = False
            rgt 1
            xlCol = T.ID
            xlCol.Font.Bold = False
            rgt 1
                xlCol = (T.PercentComplete / 100)
            xlCol.Font.Bold = False
            rgt 1
            xlCol = Int(T.Duration / 480) & " d"
            xlCol.Font.Bold = False
            rgt 1
            xlCol = T.Start
            xlCol.Font.Bold = False
            rgt 1
            xlCol = T.Finish
            xlCol.Font.Bold = False
            rgt 1
            xlCol = T.ActualStart
            xlCol.Font.Bold = True
            rgt 1
            xlCol = T.ActualFinish
            xlCol.Font.Bold = True
            rgt 1
            xlCol = T.Predecessors
            xlCol.Font.Bold = True
            rgt 1
            xlCol = T.Successors
            xlCol.Font.Bold = True
                rgt 1
                If T.Critical = True Then
                    xlCol = "Yes"
                    xlCol.Font.Bold = True
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = True
                End If
            rgt 1
            If T.Milestone = True Then
                xlCol = "Yes"
                xlCol.Font.Bold = False
                Else
                    xlCol = "No"
                    xlCol.Font.Bold = False
            End If
            rgt 1
            xlCol = T.ResourceNames
            xlCol.Font.Bold = False
            rgt 1
            xlCol = T.ResourceGroup
            xlCol.Font.Bold = False
            rgt 1
        Next Asgn
'
' Added to print the data that belongs to the Tasks that have no Assignments
'
            If T.Summary Then
                Set xlCol = xlRow.Offset(0, Columns)
                xlCol = T.WBS
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ID
                xlCol.Font.Bold = True
                rgt 1
                xlCol = (T.PercentComplete / 100)
                xlCol.Font.Bold = True
                rgt 1
                xlCol = Int(T.Duration / 480) & " d"
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Start
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Finish
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ActualStart
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ActualFinish
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Predecessors
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Successors
                xlCol.Font.Bold = True
                rgt 1
                If T.Critical = True Then
                    xlCol = "Yes"
                    xlCol.Font.Bold = True
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = True
                End If
                rgt 1
                If T.Milestone = True Then
                    xlCol = "Yes"
                     xlCol.Font.Bold = True
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = True
                End If
                rgt 1
                xlCol = T.ResourceNames
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ResourceGroup
                xlCol.Font.Bold = True
                rgt 1
            Else
                Set xlCol = xlRow.Offset(0, Columns)
                xlCol = T.WBS
                xlCol.Font.Bold = False
                rgt 1
                xlCol = T.ID
                xlCol.Font.Bold = False
                rgt 1
                    xlCol = (T.PercentComplete / 100)
                xlCol.Font.Bold = False
                rgt 1
                xlCol = Int(T.Duration / 480) & " d"
                xlCol.Font.Bold = False
                rgt 1
                xlCol = T.Start
                xlCol.Font.Bold = False
                rgt 1
                xlCol = T.Finish
                xlCol.Font.Bold = False
                rgt 1
                xlCol = T.ActualStart
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.ActualFinish
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Predecessors
                xlCol.Font.Bold = True
                rgt 1
                xlCol = T.Successors
                xlCol.Font.Bold = True
                rgt 1
                If T.Critical = True Then
                    xlCol = "Yes"
                    xlCol.Font.Bold = True
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = True
                End If
                rgt 1
                If T.Milestone = True Then
                    xlCol = "Yes"
                    xlCol.Font.Bold = False
                    Else
                        xlCol = "No"
                        xlCol.Font.Bold = False
                End If
                rgt 1
                xlCol = T.ResourceNames
                xlCol.Font.Bold = False
                rgt 1
                xlCol = T.ResourceGroup
                xlCol.Font.Bold = False
                rgt 1
            End If
    End If
Next T
Application.ScreenUpdating = True
AppActivate "Microsoft Project"
MsgBox ("Macro Complete with " & Tcount & " Tasks Written")
AppActivate "Microsoft Project"
Application.Quit

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

I have been using this on all projects I worked for.

Enjoy it!

Cheers,

Chuck