none
Cut N Paste MS Project to Excel RRS feed

  • Question

  • Hello all -

    I am searching for a solution to a very old issue I have been having.  I have been hunting the internet for a long time for the solution.

    I cut n paste my schedule into Excel with the formatting that is in my MS Project file (all the colors and fonts…)  The issue I run into is on the Predecessors and Successors.  So if I have a Pred cell with say 1701, 1710, 1720 - I get depending on how I format the cell in Excel numbers like - 170117101720 or 170,117,101,720 or some other variation other than what I need.

    Any help would be awesome!

    Tuesday, April 16, 2019 9:08 PM

Answers

  • SmokeyJoe101,

    Well....tell you what, how about I give you a macro to do the export and I'll even throw in the color formatting, just because you're such a good guy. Interested? If so, send me an e-mail. Include a sample of your file (name redaction is fine), and explain the criteria you use for the color coding. Unlike Excel, Project VBA can't "read" color formatting directly, so I'll have to use your formatting criteria to reproduce the formatting in Excel. For example, let's say you use a Flag field to indicate past due tasks. I can then read that Flag field and use it to replicate the bold red finish dates in Excel. Savvy?

    John

    • Marked as answer by SmokeyJoe101 Monday, June 3, 2019 3:58 PM
    Thursday, April 18, 2019 11:17 PM

All replies

  • SmokeyJoe101,

    What version of Project/Office are you using again?

    How exactly are you formatting the cell in Excel? When I try a copy and paste of a predecessor field, it pastes exactly as it should, which is text.

    For reference, copy and paste is normally straightforward but not a very efficient way to get data from Project to Excel. What exactly are you trying to do?

    John

    Tuesday, April 16, 2019 9:44 PM
  • Office 365 Excel and MS Project Pro 2016

    Every week I put my schedule into Excel to send out to the team for their updates.  There are different color used to indicate late task, coming up task, summary task, lets say some special task....  If I do an export the numbers come out OK, it I do a copy n paste the numbers get messed up.  I have tried all sorts of variations on formatting of the cells in Excel with no luck. 

    Wednesday, April 17, 2019 2:55 PM
  • SmokeyJoe101,

    How exactly do you do an "export"? Why do you sometimes do an export and other times do a copy/paste?

    What Project fields do you need in Excel?

    John

    Wednesday, April 17, 2019 5:03 PM
  • I actually never do an export, I have just tried it to see if it made a difference.

    UID, ID, Task Name, %Complete, Start, Finish, Pred, Suc, Resource

    Wednesday, April 17, 2019 7:03 PM
  • SmokeyJoe101,

    Okay, but that didn't answer my question. When you did try it, exactly how did you do the export?

    And I forgot to ask, you mentioned using different colors to represent certain tasks or special tasks, is the whole task line colored or just certain fields on the line and if fields, which ones? And how important is the color coding? If I could give you an easy way to get your Project fields to Excel without color coding, might that work for you?

    John

    Wednesday, April 17, 2019 7:47 PM
  • John,

    I used the "Save Project File as Excel" Under "File" - "Export" - When I do it this way, the numbers in the "Pred/Suc" are correct/but color coding is gone.

    The colors are for entire rows and then Finish dates that are past due are bold and red

    The Team and I rely on the colors to much to institute a change.

    Thursday, April 18, 2019 10:59 PM
  • SmokeyJoe101,

    Well....tell you what, how about I give you a macro to do the export and I'll even throw in the color formatting, just because you're such a good guy. Interested? If so, send me an e-mail. Include a sample of your file (name redaction is fine), and explain the criteria you use for the color coding. Unlike Excel, Project VBA can't "read" color formatting directly, so I'll have to use your formatting criteria to reproduce the formatting in Excel. For example, let's say you use a Flag field to indicate past due tasks. I can then read that Flag field and use it to replicate the bold red finish dates in Excel. Savvy?

    John

    • Marked as answer by SmokeyJoe101 Monday, June 3, 2019 3:58 PM
    Thursday, April 18, 2019 11:17 PM
  • John

    Sounds good, I will work on the criteria and get it over to you.  Do you need to know the specific color names?

    Friday, April 19, 2019 1:00 PM
  • SmokeyJoe101,

    I'll look for your message. Yes, specific color names would help.

    John

    Friday, April 19, 2019 3:04 PM
  • John - still working on it


    Sent - sorry so long
    Monday, April 22, 2019 8:49 PM
  • John - I can't thank you enough.

    John was able to provide me with a solution for my Cut-N-Paste issue. John also gave me a tool that creates special formatting in MS Project and Exports that to Excel, going above and beyond my expectations. While creating this macro he also caught a small error in one of my formulas.  The macro works flawlessly every time without human error and saves me time!

    • Marked as answer by SmokeyJoe101 Monday, June 3, 2019 1:48 PM
    • Unmarked as answer by SmokeyJoe101 Monday, June 3, 2019 1:49 PM
    Monday, June 3, 2019 1:48 PM
  • SmokeyJoe101,

    You're welcome and thanks for the feedback.

    John

    Monday, June 3, 2019 4:04 PM