INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Remember Me

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Just copy and paste the

# Convert to Excel Without Reformatting

Share

## Convert to Excel Without Reformatting

(OP)
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

### RE: Convert to Excel Without Reformatting

(OP)
Hi,

Does anyone have any suggestions?

Thanks

### RE: Convert to Excel Without Reformatting

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.

### RE: Convert to Excel Without Reformatting

(OP)
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?

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

(OP)
Hi ierland & others,

Is there a solution for this?

Thanks

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

(OP)
Hi,

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

Thanks

### RE: Convert to Excel Without Reformatting

(OP)
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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

(OP)
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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

(OP)
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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

(OP)
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

### RE: Convert to Excel Without Reformatting

(OP)
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.

### RE: Convert to Excel Without Reformatting

(OP)
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?

### RE: Convert to Excel Without Reformatting

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.

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

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

### RE: Convert to Excel Without Reformatting

You could use this macro listed below.

#### CODE --> VBA

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

Sub ExtractMSP()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Proj As Project
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
Cells.Select
ActiveSheet.Paste
Range("A1").Select
ColumnCount = 0
TRowCount = 1
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 2
xlCol = "WBS"
rgt 1
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

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

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!