Convert to Excel Without Reformatting

Question

Convert to Excel Without Reformatting

REDesigner09 (Aerospace)

(OP)

3 Jan 12 19:53

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

REDesigner09 (Aerospace) · Answer 1 · 2012-01-11 13:20:54

Hi,

Does anyone have any suggestions?

Thanks

ierland (Computer) · Answer 2 · 2012-03-02 17:10:40

What do you exactly mean? While exporting to Excel you do not want only the data but also some colours and other formatting stuff from MS Project?
What do you exactly want to Export? Have you alreday looked into the graphical reports?
An alternative might be some template in Excel with conditional formatting, based on e.g. overview level, task or milestone, summary yes/no etc.
Perhaps upload a small example + a clear wish list.

REDesigner09 (Aerospace) · Answer 3 · 2012-03-02 21:58:59

Hi,

  With MS Project (2007), when copying & pasting its contents into Excel, the format changes - looses colors, column sizes, etc., which for the majority of this, is no big deal.

  However, the durations from MS Project gets converted in Excel showing both date & time.  Although doable, I can extract the date in Excel with a function or two.

  I'm trying to verify the durations & have the ability to manipulate if needed.  However, I can't do this "cleanly" & seeking ways to do so.

  What do you suggest?

ierland (Computer) · Answer 4 · 2012-03-03 06:24:45

Currently I do not have MS Project 2007 readily available. Next week I hope to be able to look into this.

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

REDesigner09 (Aerospace) · Answer 5 · 2012-03-16 14:19:22

Hi ierland & others,

Is there a solution for this?

Thanks

ierland (Computer) · Answer 6 · 2012-03-17 19:29:21

Hi,

Finally today I could get hold of a 2007 version. I am now not sure what you exactly are talking about. Can you upload a small example, have an URL for it or send me a private message for sending a file so that I can look into your problem in more detail. Perhaps indicate then as precise as possible what you want to achieve.

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

ierland (Computer) · Answer 7 · 2012-03-23 17:08:00

Are you no longer interested in any help or did you resolve the issue yourself? In the latter case I would be interested in your approach.

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

REDesigner09 (Aerospace) · Answer 8 · 2012-03-23 23:47:36

Hi,

I've been away for a while. I'll create something & upload shortly.

Thanks

REDesigner09 (Aerospace) · Answer 9 · 2012-03-27 11:10:50

Hi ierland,

Attached is an example Excel file of extracted data from MS Project 2007.

  As you will see, columns B through J is extracted data or data that was copied & pasted from MS Project into Excel.

My columns M through O are excel functions extracting the date only or removing the time that MS Project seems to give, even though I don't care to have.

  The intent of this document is see how my durations are adding up between Planned Start & Finish.  I also planed to do some other things with this Excel file, such as trying to modifying the dates to get more desirable project plan.

  It would nice if when copying & pasting from MS Project to Excel, it would keep the same MS Project format - Showing primary tasks & sub-tasks & so forth.

  Currently, it does not & unless I manually modify through Excel, I do not see an option to keep the same formatting.

What are you suggestions?

Thanks

http://files.engineering.com/getfile.aspx?folder=f02725c6-a057-4f16-9161-1d

IRstuff (Aerospace) · Answer 10 · 2012-03-27 16:22:46

It would be useful to see what the project file looks like

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

ierland (Computer) · Answer 11 · 2012-03-27 17:31:29

It is a pity that you did not add the corresponding MS Project file.
I am not sure if you have used an import export map.
Your dates would not be a problem. Just use the correct formatting in Excel to "remove" the hours.
In MS Project Duration is given in WORKING DAYS (or weeks, etc.) [normally of 8 hours a day] You appear to like the difference in Calendar days. That is another value. In MS project you could also add a Custom Field with the difference in Calendar days.
It is not clear for me how you want the MS Project structure be reflected in Excel. You could easily also download Overview Level. I use that if I want Summary Tasks in a different column [on a separate Workbook Sheet] This could be achieved with rather simple expressions.
Do you want to merge values from Excel with MS Project? In that case I recommend also to include UniqueID.

Some field require a little bit of magic in Excel but most fields you can easily import or export.

I am, however, curious what you want to achieve in Excel compared to directly manipulating in MS Project. If it is just about dates rather than changing Predecessors and Successors, why not use an Interim Baseline? You can always return to your old situation. On the other hand maintenance of a project plan means constantly updating the existing information in MS Project.

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

REDesigner09 (Aerospace) · Answer 12 · 2012-03-27 20:33:39

Hi ierland,

  I was looking for the MS Project, but appears I deleted it.  I will make another & submit with Excel file to see what you can do.

  As explained before, I just simply copied from MS Project into Excel.

  When I do this, I loose the WBS structure & bit of the formatting.  I don't mind loosing some formatting, but I don't want to loose all the formatting.

  As explained above, the main purpose is so I can mimic the MS Project & put some other parameters & or charts to the project plan.

Thanks

ierland (Computer) · Answer 13 · 2012-03-28 03:33:35

As you can make visual reports with MS Project 2007 and anyhow can add Custom fields, I am still wondering what you want to achieve in Excel that would not be possible in MS Project.

There are sometimes text changes I will do in Excel, just because it is easier there to do text manipulation. However, in general, MS Project is THE product to do anything about scheduling.

Therefore I am very curious to know what you would do in Excel that is not possible in MS Project, apart from some report charts. That possibility is now incorporated in MS Project 2007 and above.

Up to now, only the text manipulation and some pre-calculations before entering data into MS Project, have been for me the only needs to use Excel as well.

There might be a potential other use. If people don't have MS Project, an export to Excel might perhaps be useful. However, normally I would make a pdf-file.

Theoretically one could export to Excel, change some data and merge these again with the plan in MS Project. If remote amendment [such as Actual Work] would become a frequent goal, there was in the past an easy Outlook tool. Today MS makes is necessary to use the expensive and complicated MS Project Server. If you would have many project with shared resources on several locations with a need to constantly updating your projects, then MS Project Server may become essential.

For simpler combinations of projects a resource pool may do. I get the feeling, perhaps wrongly, that you have just a rather simple project, being more used to Excel working with that where probably MS Project itself could help you as well. This is not uncommon, as most people believe that they can just start using MS Project and real good (advanced) MS Project courses are more or less rare.

I am still awaiting both files and a more detailed explanation what you want to achieve. Maybe I can be of any help although miracles are not always readily possible. Others could also contribute with good solutions as there is hardly ever a single solution for a given problem. A lot remains a matter of appreciation. That could also apply to your wish to use Excel.

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

ierland (Computer) · Answer 14 · 2012-04-16 08:40:37

Do you expect to provide a sample MS Project file + explanation why export to Excel is necessary or have you forgotten about the issue?

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

REDesigner09 (Aerospace) · Answer 15 · 2012-06-09 23:00:39

Hi ierland,

Sorry, I've been away from this forum for a while. Are you still interested with assisting me? If so, I'll submit something.

Thanks

ierland (Computer) · Answer 16 · 2012-06-10 04:52:39

Although you are not very fast in responding I would still be prepared to look into your problem, although without any guarantee that I can solve it. I will be away a few days next week as well. However, the sooner you upload something the faster I can find some spare moments. In addition can you explain what you want to do in Excel that is not possible within MS Project? I know there are some (rare) cases. So I am always curious to know your application.

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

REDesigner09 (Aerospace) · Answer 17 · 2012-06-14 02:28:14

Hi ierland,

It took a while, but I finally took some time out of my very busy schedule to submit something. I know you are also quite busy, but your assistance is greatly appreciated.

I made a bogus project up within MS Project which is a typical layout of one of my projects. I have a target completion date (10/01/12) in which I'm trying to define a series of deliverables or tasks between the start & finish dates.

Additionally, I'm trying to demonstrate or show project progression by showing planned vs. actual durations.

Perhaps because I'm better with using Excel, I am also trying to use it to manage my projects. So, you will see within the Excel file, I extracted the raw data & pasted it in. I have some columns to the right of the table which can convert the dates-time to just dates.

It's not shown here, but if I can extract the MS Project raw data "cleanly" enough here, I can also do a number of things to produce task warnings or show a number of different charts. As you will see, my color formatting from MS Project to Excel is lost. Not a huge deal because the data is more important, but it would be nice to keep it.

Please review attachments & provide feedback.

Thanks again...

Perhaps this bogus project plan is too simplified, but with bigger ones, but you should g

http://files.engineering.com/getfile.aspx?folder=04cd34ae-df44-4be8-a3f7-09

REDesigner09 (Aerospace) · Answer 18 · 2012-06-14 02:31:52

Oops,

Part of my message got cut off & it does not appear that I could upload 2 documents at once.

Here's the message:

It took a while, but I finally took some time out of my very busy schedule to submit something. I know you are also quite busy, but your assistance is greatly appreciated.

I made a bogus project up within MS Project which is a typical layout of one of my projects. I have a target completion date (10/01/12) in which I'm trying to define a series of deliverables or tasks between the start & finish dates.

Additionally, I'm trying to demonstrate or show project progression by showing planned vs. actual durations.

Perhaps because I'm better with using Excel, I am also trying to use it to manage my projects. So, you will see within the Excel file, I extracted the raw data & pasted it in. I have some columns to the right of the table which can convert the dates-time to just dates.

It's not shown here, but if I can extract the MS Project raw data "cleanly" enough here, I can also do a number of things to produce task warnings or show a number of different charts. As you will see, my color formatting from MS Project to Excel is lost. Not a huge deal because the data is more important, but it would be nice to keep it.
Perhaps this bogus project plan is too simplified, but with bigger ones, there will be many more layers. With these, I want to also be able to manipulate my dates & then past in or manually adjust dates within MS to get desired plan & durations.
Please review attachments & provide feedback.

Thanks again...

I seem to be having issues uploading a MS Project file. From the Excel file, it should be simple enough to duplicate though.

REDesigner09 (Aerospace) · Answer 19 · 2012-06-16 10:42:53

Hi ireland & Others,

Not sure why, but for some reason, I can't upload the MS Project file.

To re-iterate my intents:

I would like to take my full MS Project layout or plan & past its raw data into Excel. Once I can get the data "cleanly" extracted, I use Excel functions to extract the dates. With this, I can produce a handful of other metrics or perform some verifications that I can't do within MS Project.

I just need a method to not have Excel (radically) modify the layout or to keep all the WBS levels & its indentations.

Is this possible?

http://files.engineering.com/getfile.aspx?folder=6bc00d66-db24-4d40-b0bc-10

SueK (Mechanical) · Answer 20 · 2012-07-13 05:06:37

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 (Aerospace) · Answer 21 · 2012-08-18 02:14:20

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 (Computer) · Answer 22 · 2012-08-24 18:22:52

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 (Computer) · Answer 23 · 2012-09-27 23:11:22

You could use this macro listed below.

CODE --> VBA

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

Come Join Us!

Posting Guidelines

Related Projects

Jobs

Convert to Excel Without Reformatting