• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

linking Project to Excel

G

greg.begin

Member
I am trying to create metrics from my project file, but i am having trouble linking to my project file for automatic updates. can someone help please. I have done a copy and paste link text but only certain columns are pasted and not everything i copied.


Thanks
 
Hello Greg,

Could you provide some additional details on what you were trying to copy, what got copied, what did not get copied, etc.?


By the way, when you paste a link from Project into Excel, I don't believe you would be able to reference that data from Excel formulas. You may want to look into "copy/paste as HTML".


-Sajan.
 
I got the copy to work, here is the new problem. i keep getting reference issues now. it will work one moment then not another. i will look at the html idea and get back with you.


thanks
 
Hi, Greg!

I don't have neither MS Project installed here nor any Project files. Sorry for not being able to help you.

Regards!
 
Hi Greg,

Could you provide some specifics of what you are trying to do, and how you are attempting to do it?


I have Project 2010 on my PC and I am able to copy/paste data from Project to Excel (2010) without any issues.


When you say you are getting an OLE error, how were you copying the data from Project?


If you can provide more details, I may be able to help.


-Sajan.
 
Hello Sajan,

I am at a production facility. we have decided to go forward to provide MS project schedules for each end item. there are about 700 individual tasks that need to be accomplished. from these project files i need to gather schedule information as well as % complete information. so what i did was create a custom view in MS project so that all i need to do is open the file and copy each field that I need. I then did a paste special link on Unicode Text for automatic updates in my excel file. i then take this info and create dashboards and status metrics. the problem i am having is the link constantly breaks and then i recieve a Ref!# error on every link on the excel file. i am using MS Project and Excel 2010. What i don't want is to have to open the project file and copy and paste everytime i want an update.


the OLE error, i discovered, was becuase when i went to a different computer, i didn't have MS Project 2010 installed and could not read the file. so to mitigate this, i saved all my schedules in Project to 2007 compatible. Please help for I am severely behind schedule to make this work.


Thank you,


Greg
 
Hi Greg,

I just tried the paste/link operation you described, and I got a similar error. Apparently, the link only worked while I had the Project plan doc open. After I closed the project plan doc, the link stopped working, even when I attempted to "refresh" the links. (I had never tried this approach before, so I don't have any insights into why the paste/link does not work, other than to say that integration between MS Project and other Office apps have never been that good.)


We may need to explore alternate ways to accomplish what you are trying to do.


As I understand from your post, you are wanting to have an extract of the tasks from one or more Project plan documents, and collect schedule info and % complete info outside of Project. Is that correct? (I am assuming that you are planning to update this info back in your Project doc after you have collected them through Excel. I am also assuming that you are using this approach since you are asking your workers to fill in the required info in Excel, and you are planning to consolidate the updates into Project.)


Before exploring options for automation, please confirm that the following process would work for you:

[list type=decimal]
[*]Create a view in Project that includes the "GUID" field. GUID is a unique identifier that is guaranteed to remain unique across different Project docs, etc.
[*]Copy the desired data from Project and paste into Excel. "Paste as HTML" is the default paste operation. As such, you should be able to simply paste into Excel.
[*]Add formulas in Excel sheet to clean up / convert any fields (such as the date fields that were pasted as text, etc.)
Make your updates in Excel (such as % complete)
If you paste the data from Project into Excel again, identify and remove duplicates, using the GUID field as the tie-breaker.
Once you are ready to update the Project document, use the GUID to match the updates from Excel to Project.
[/list type=decimal]

I realize that this is a very manual process... but if this works for you, we can explore ways to automate this (by checking which tasks already exist in the Excel doc, etc.)


-Sajan.
 
I am having the same issue as Greg and Sajan.

From MS Project 2010 I copy a cell and then goto Excel 2010 to a paste special as link on Unicode Text. It works well as long as the Project plan doc is open. After I closed the project plan doc, the link stops working and I see a Ref!# error on the linked cell in excel sheet.


How can you solve this?


Thank you!!

Ankur
 
Good day ank000


It is not a good idea to post a question into someone else's post, this post is a few months old and those that helped out the OP may not vist this post again as they will assume they have helped all they can. A new question...a new post..with a good descriptive header.
 
You must log in or register to reply here.
Back
Top