none
Can I link an Excel 2010 spreadsheet or ODBC database into Project Professional 2010? RRS feed

  • Question

  • I have used previous versions of MS Project, but am new to Project Professional 2010. I am currently managing the creation a new scheduling system for the company. We use MAS 200 for material ordering, tracking, etc. My company wants me to be able to integrate this information (materials, purchase order info, expected delivery date, etc.)  into my project schedules. I have not done this in the past and am having a great deal of difficulty determining if this is even possible. I am able to query this information into an Excel spreadsheet without any problems. I would like to be able to import this information into MSP as well. Is this possible? I have used the Import Wizard without any positive results. I do not believe that my company has Project Server capabilities available, would this help? I would appreciate any guidance or insight from anyone who has encountered a similar challenge.

    Friday, May 4, 2012 9:20 PM

Answers

  • KAR_120_C,

    It sounds like a lot of the data you intend to import is ancillary and should really go into the Notes field (i.e. vendor names, dimensions, lengths, dates for receipt, quantities etc.).

    You might find the information in this post helpful, http://answers.microsoft.com/en-us/office/forum/office_2010-project/import-format-for-project/72bc1222-ca2b-427c-b1f4-43cd131f718e.

    John

    Saturday, May 5, 2012 12:53 AM

All replies

  • KAR_120_C,

    You can import ODBC database data into Project but it requires VBA. However, since you apparently have the data in Excel and tried using the import wizard, what problems did you encounter? More details on exactly what you are trying to import into which field would be most helpful.

    Just for reference, import from Excel into Project is not straightforward.

    John

    Friday, May 4, 2012 9:38 PM
  • John, thanks for your response.

    1. When I try to link to my Excel spreadsheet that is linked via ODBC, Project crashes.

    2. When I try to link Project directly to the ODBC I have done the following and nothing has worked. I have imported using both the Append and Merge data selections on the wizard. I select Resources next. I use the pull down menu for source table selection which gives me all of the data fields from the program I am trying to import from. I select the appropriate fields I want to use and match them with custom columns I created in the Resource Sheet. Everything matches in the From Database Fields and To Microsoft Project Fields, the preview also matches. It tells me to specify a primary key in order to merge data into an existing project. I set one. I click Finish and nothing is ever imported into the fields in my resource sheet.

    I am learning the hard way how difficult it is to do this, perhaps it is not possible. Would utilizing Project Server help? I don't think this company is set up for that.


    jjjjj

    Friday, May 4, 2012 10:03 PM
  • KAR_120_C,

    Let me correct something I stated earlier. I have only imported ODBC data from an Access database using VBA. I've never tried it using the import wizard.

    Did you use the import wizard for the import from Excel? You mention something about linking the Excel spreadsheet and importing does not establish any links, it is more like a structured copy and paste.

    Something else I should ask. Do you have SP1 installed for Project 2010 and Office 2010? If not, go to, http://technet.microsoft.com/en-us/office/ee748587, download and install them.

    You shouldn't need Project Server to do what you want although it may be easier. I don't use Project Server so I can't give you anymore insight on that but if the company you are working with doesn't have Project Server anyway, it is a mute point.

    John

    Friday, May 4, 2012 10:31 PM
  • John,

    I used the Query Wizard to import the data from the database into Excel. I will have to look into what has or has not been installed. I just started this job and am utilizing what was here when I got here last week. Thanks for the link I will try that. I am not familiar with and have not used VBA in the past.

    When I use the import wizard in Project, nothing happens at all. No data is brought in or pasted at all, if I understand your comment about structured copy and paste correctly.

    Sounds like this may be something that is beyond the capabilities of Project. If that's the case I just want to know so I can stop wasting my time.


    jjjjj

    Friday, May 4, 2012 10:40 PM
  • Import from Excel should probably work.  That being said, I've found the easiest way to handle this sort of scenario is using VBA to import from a SQL database.  That of course, is dependent on your SQL skills, but I've found embedding SQL queries within VBA to be relatively simple.

    Everything you mentioned seems eminently doable.  The question may be more about what fields you're trying to import to within Project.  Perhaps you could mention the specific fields you're trying to import into?  Are they all custom fields, or are you manipulating Cost, Actual Cost, etc.


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Friday, May 4, 2012 10:50 PM
    Moderator
  • I am not a computer programmer, I am a project manager. However, I am technically savvy and will look into the best way of doing this. My challenge has been trying to figure this one out.

    The fields I want to import into would be nearly entirely custom since the information I want to import does not exactly coincide with the standard fields in the Resource Sheet. Perhaps this is the issue.

    I have a question though. If I can somehow get this information into Project, would I have to keep importing each time I want to update with current information, or could it update automatically to reflect real time data from the database of origin? 


    jjjjj


    • Edited by KAR_120_C Friday, May 4, 2012 10:58 PM
    Friday, May 4, 2012 10:55 PM
  • Import from Excel still sounds like your best bet then.  Perhaps you're experiencing a type mismatch?  What kind of fields are you importing?  Cost, Duration, Time, etc.?

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Friday, May 4, 2012 10:58 PM
    Moderator
  • It is a mix of several, types. Duration, text, etc. For example I have numeric job numbers, vendor names, alphanumeric vendor IDs, item codes (which can be numeric, alphanumeric or text), item descriptions which include all manner of information numerical and text (dimensions, lengths, etc.), dates for receipt and due dates for material, and quantities ordered and received which can be whole numbers or include decimals. I have suspected this may be a possible problem since the data can be somewhat inconsistant even within it's respective field.


    jjjjj

    Friday, May 4, 2012 11:07 PM
  • KAR_120_C,

    It sounds like a lot of the data you intend to import is ancillary and should really go into the Notes field (i.e. vendor names, dimensions, lengths, dates for receipt, quantities etc.).

    You might find the information in this post helpful, http://answers.microsoft.com/en-us/office/forum/office_2010-project/import-format-for-project/72bc1222-ca2b-427c-b1f4-43cd131f718e.

    John

    Saturday, May 5, 2012 12:53 AM