Share via

Importing vendor project plan from Excel; predecessors with lead/lag do not import correctly

Anonymous
2011-01-25T00:55:55+00:00

Hi,

Our vendor uses Microsoft Project Server and exports a master plan (consisting of 50+ individual project plans, totaling over 25,000 lines) to an Excel file for our PMO. We are trying to import the plan back into Project Professional 2007 for ease of management and readability; at over 25,000 lines, the Excel file is difficult to manipulate, to say the least.

I've noticed that the Import Wizard takes care of all task, date, and custom text fields with no issues. Where we run into a problem is with tasks that have predecessors with lead/lag (i.e. 25FS+1w). Either no value imports into the predecessor field where this occurs, or an incorrect value appears.

Has anyone else seen this? Is there a specific format which should be used for predecessors with lead/lag? I've tried removing spaces, i.e. changing "25FS + 1w" to "25FS+1w", but no success. I also downloaded, customized and test the managed code add-in using VB... still no success. Any tips or suggestions would be much appreciated.

Thanks,

Carissa Letz, PMP

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

John Project 49,705 Reputation points Volunteer Moderator
2011-01-27T00:50:50+00:00

Carissa,

I understand the issue with sensitive data. That has been brought up many times in the newsgroup/forum. For reference, I and MVP Jack Dahlgren separately developed macros several years ago that will purge a Project file of sensitive data. However I assume that door is already closed.

I can't imagine what add-on is necessary to deal with cross-project links, Project handles those quite nicely if some groundrules and discipline are used. The only issue I'm familiar with in a master file with cross-project links is trying to convert that dynamic master into a static master and maintain the complete link structure. Years ago I wrote a macro to do exactly that, but I don't see how it would help in your case. It sounds like the macro you use in Excel works similar to the macro I have but my macro works directly in Project and doesn't need to use the Unique ID fields.

Without actually having access to your Excel file and supplementary data, it is a little difficult to troubleshoot why your import doesn't work. Did you get a chance to try importing through the Notes field as I suggested previously?

Unless we can find a more elegant solution, something you said earlier lead me to think of a "back door" approach. You mentioned that you could filter out an individual project from the Excel data and make the import work without problems. How about an approach wherein the process is repeated for all 50+ subprojects using the append option on the import? Ugh! you say, "don't want to do that every week". And I would agree, you don't want to manually do 50+ imports every week. So you automate it with VBA. Think about it.

John

Was this answer helpful?

0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-01-31T14:42:28+00:00

    Hi John,

    Thanks very much for your help. Since my original post, a new Excel file was released from the vendor, which I was able to successfully import with no issues. I also went back to a previous version and was able to successfully import that version as well.

    My only conclusion is that the file I was unable to import had some kind of bad data hidden somewhere within the file. Your suggestion of importing each subproject separately is probably the only way to even begin to isolate the bad data. My concern with that is that due to the cross-project links, additional issues may occur if the subprojects aren't imported in the right order. For the time being, we're just chalking it up to bad source data and hoping the issue doesn't recur.

    Thanks again for all your effort.

    -Carissa

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-01-26T18:24:37+00:00

    The vendor has cost and resource data in the files, which they don't want us viewing. Also, they are using at least one third-party add-on for cross-project links (more on that later) which causes issues when we try to view the MPPs without the add-on. Basically, we've been told they can't/won't provide us the MPP files; hence the need to import back from Excel.

    The extra field(s) have been added to the Excel worksheet to deal with the ID and Predecessor data. Since the source project is a dynamic master with non-unique task IDs, there was a need to create unique task IDs (the vendor either couldn't or wouldn't use the unique ID field from the master). The macro "translates" the in-plan predecessors' task IDs to the corresponding unique task IDs, ensuring that MSP sees the correct predecessor tasks.

    There is also a separate report we receive which lists the cross-project links. Those links are added to the list of in-plan predecessors for each task.

    The goal is to have a single static master in MSP. Simple, right? ;-)

    Was this answer helpful?

    0 comments
  3. John Project 49,705 Reputation points Volunteer Moderator
    2011-01-26T02:43:06+00:00

    Carissa,

    First let me ask an obvious question. Why don't you simply take the vendor's 50+ files as regular Project files and then create a master by inserting them? Or, if done properly you could even take his master with subproject directly. You just have to make sure you have a folder structure set up the emulates his folder structure so all the links are retained. The extra processing through Excel seems unnecessary and is obviously complicating things.

    I'm not quite clear on the extra field you've added. Is it in the vendor's file? Do you add it to the Excel Worksheet? Also, what is the purpose of macro that parses and then concatenates the predecessor data? Is that macro in Excel?

    Do the vendor files have cross-project links or are all links (simple and complex) only from task to task within each subproject?

    Is your end goal to replicate a dynamic master from the Excel data or are you only looking for a static master?

    I assume you are aware that copy and paste has a 255 character limitation for all text fields in Project. The exceptions are: Notes, Predecessor, Successor fields. Although I haven't actually tried it, I assume the UniqueIDPredecessor and UniqueIDSuccessor fields will also accept more than 255 characters.

    John

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-01-26T00:27:29+00:00

    One other note I should mention... when I initially noticed the issue, I attempted to copy/paste the values from Excel into the MSP Unique ID Predecessors field. It didn't work. There was no error, but the incorrect imported values did not update with the correct, pasted values. This was the case for copy/pasting the column as well as within individual tasks.

    Thanks,

    Carissa

    Was this answer helpful?

    0 comments