Convert to Excel Without Reformatting
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
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
Does anyone have any suggestions?
Thanks
RE: Convert to Excel Without Reformatting
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
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
Why an easy solution if you can make it complicated?
Greetings from the Netherlands
RE: Convert to Excel Without Reformatting
Is there a solution for this?
Thanks
RE: Convert to Excel Without Reformatting
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
Why an easy solution if you can make it complicated?
Greetings from the Netherlands
RE: Convert to Excel Without Reformatting
I've been away for a while. I'll create something & upload shortly.
Thanks
RE: Convert to Excel Without Reformatting
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
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
RE: Convert to Excel Without Reformatting
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
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
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
Why an easy solution if you can make it complicated?
Greetings from the Netherlands
RE: Convert to Excel Without Reformatting
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
Why an easy solution if you can make it complicated?
Greetings from the Netherlands
RE: Convert to Excel Without Reformatting
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
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
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
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
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
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
CODE --> VBA
I have been using this on all projects I worked for.
Enjoy it!
Cheers,
Chuck