Dynamically link MS Project Resource Usage Data to Excel

Question

Hello,

Is there any way to link the MS Project Resource Usage data directly in an Excel spreadsheet, such that the Excel spreadsheet updates every time an update is made to the Resource Usage sheet?  

At present I am exporting the Resource Usage data from MS Project, then applying formatting, formulas, etc. However, when I make a change to the Resource Usage data, I need to export the data into Excel again and re-format.

My resource usage data is changing frequently at present and therefore I have cause to run this export several times, and then reformat.  Is there any way to dynamically link the resource usage data from MS Project to Excel?

Currently using MS Project 2010 and Excel 2010.  

Thanks in advance for any insight.

***Post moved by the moderator to the appropriate forum category.***

Answer 1

coralbubble,

Is there a way, well yes, but you really don't want to do it. Why? Because dynamic links between applications (e.g. Project and Excel) are prone to corruption.

Your best bet is to either become familiar with and use Pivot Tables in Excel so you can use Project's Visual Reports, or as Trevor mentioned use VBA. Neither of those approaches will give you an automated update, well, an Event based macro could do it but that can get a little complicated. My suggestion is to use VBA (not Event based) to develop a macro that transfers the data you want and also formats it in Excel. The transfer could be initiated with a single custom button on Project's ribbon.

John

Answer 2

Start by copying just one cell value from MSP and then go to whichever cell in EXCEL and right click and choose hyperlink. Then the cell in EXCEL is dynamically linked to a cell in MSP.

But it sounds to me that you may many cells to copy/paste, such as a row or several from resource usage. Theoretically possible but difficult to line up and keep track of.

Otherwise a VBA macro?