SOLVED

Add, Edit, Delete Project Online data using Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-856434%22%20slang%3D%22en-US%22%3EAdd%2C%20Edit%2C%20Delete%20Project%20Online%20data%20using%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856434%22%20slang%3D%22en-US%22%3E%3CP%3ETeam%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20aware%2Fcomfortable%20accessing%20our%20Project%20Online%20data%20using%20the%20ODATA%20-%20I%20set%20up%20connections%2Fqueries%20using%20Excel%20365%20and%20Power%20Query%20etc..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'd%20like%20to%20do%20-%20is%20after%20loading%20the%20data%20from%20let's%20say%20the%20Projects%20object%20-%20I'd%20like%20to%20modify%20the%20project%20data%20values%20in%20excel%20-%20much%20easier%20than%20doing%20it%20in%20MS%20Project.%20For%20example%2C%20what%20if%20I%20wanted%20to%20change%20the%20Start%20and%20Finish%20Dates%20to%20be%20the%20value%20they%20are%20now%20PLUS%2075%20days.%20Easy%20in%20Excel%20to%20do%20a%20formula%2C%20then%20update%20the%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20this%20with%20other%20tools%20for%20applications%20like%20SalesForce%20etc%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20wondering%20is%20if%2Fhow%20-%20after%20I%20make%20these%20edits%20-%20what%20can%20I%20do%20to%20then%20PUSH%20the%20edits%20back%20into%20Project%20online.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%2C%20my%20question%20makes%20sense%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-856434%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAPI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOnline%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EProject%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856651%22%20slang%3D%22en-US%22%3ERe%3A%20Add%2C%20Edit%2C%20Delete%20Project%20Online%20data%20using%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856651%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402624%22%20target%3D%22_blank%22%3E%40MonteStJohns%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECopied%20my%20reply%20from%20TechNet%3A%3C%2FP%3E%0A%3CP%3EIt%20feels%20like%20you%20could%20be%20over%20engineering%20something%20here%2C%20what's%20the%20issue%20with%20project%20managers%20maintaining%20their%20project%20schedules%20in%20Microsoft%20Project%20%2F%20PWA%3F%20Depending%20on%20what%20difficulties%20your%20users%20are%20having%2C%20it%20might%20be%20easier%20to%20build%20an%20add-in%20for%20Microsoft%20Project%20using%20VBA%20and%20keep%20it%20all%20in%20Project%20as%20you%20then%20do%20not%20have%20any%20data%20syncing%20challenges%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Foverview%2Fproject%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Foverview%2Fproject%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857179%22%20slang%3D%22en-US%22%3ERe%3A%20Add%2C%20Edit%2C%20Delete%20Project%20Online%20data%20using%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F8793%22%20target%3D%22_blank%22%3E%40Paul%20Mather%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20prompt%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20this%20time%20-%20I'm%20in%20the%20embryonic%20stage%20of%20trying%20to%20use%20PWA%2FPO%20for%20initially%20Portfolio%20Management%20-%20with%20first%20focus%20on%20Resource%20Management%20and%20Portfolio%20Prioritization.%20We%20don't%20even%20have%20enough%20licenses%20NOR%20have%20we%20trained%20PMs%20to%20use%20any%20MS%20Project%20suite%20of%20tools.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20first%20thing%20I'm%20trying%20to%20do%20-%20is%20get%20the%20%22single-source%22%20of%20data%20out%20from%20100's%20of%20separate%20XLS%20files%20into%20PWA%2FPO.%20So%2C%20me%20and%20my%20first%20EPPM%20team%20member%20are%20doing%20ALL%20the%20work%20by%20ourselves%20-%20and%20it%20will%20be%20that%20way%20for%20at%20least%20the%20next%2090-120%20days%20as%20we%20first%20have%20to%20SELL%20the%20approach%2C%20then%20assuming%20it%20works%2C%20then%20we%20have%20licensing%2C%20training%20etc%20until%20we're%20at%20the%20point%20we're%2030-45%20individuals%20are%20creating%2Fmaintaining%20the%20content.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20link%20(%3CA%20href%3D%22https%3A%2F%2Fwww.xappex.com%2F%23%2FEnabler4Excel%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.xappex.com%2F%23%2FEnabler4Excel%3C%2FA%3E)%20to%20the%20tool%20I've%20used%20in%20my%20past%20to%20do%20similar%20work%20at%20different%20organization%20when%20we%20were%20using%20SFDC%20as%20the%20primary%20development%20platform%20for%20many%20business%20process%20(including%20EPPM).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20be%20at%20the%20office%20in%20an%20hour%20or%20so%20and%20will%20look%20closely%20at%20the%20links%20you%20provided.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20always%2C%20appreciate%20your%20prompt%2Fcandid%20feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMonte%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-858272%22%20slang%3D%22en-US%22%3ERe%3A%20Add%2C%20Edit%2C%20Delete%20Project%20Online%20data%20using%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858272%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402624%22%20target%3D%22_blank%22%3E%40MonteStJohns%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3EYou%20could%20probably%20automate%20this%20import%20if%20you%20have%20consistent%20source%20data%20across%20all%20the%20Excel%20files%20but%20this%20would%20be%20custom%20code%20-%20potentially%20VBA%20%2F%20VSTO%20add-in%20for%20Excel%20%2F%20Project.%20Out%20of%20the%20box%20you%20do%20have%20the%20ability%20to%20import%20Excel%20data%20to%20Project%20too%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fimport-excel-data-into-project-cb3fb91a-ad05-4506-b0af-8aa8b2247119%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fimport-excel-data-into-project-cb3fb91a-ad05-4506-b0af-8aa8b2247119%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-858870%22%20slang%3D%22en-US%22%3ERe%3A%20Add%2C%20Edit%2C%20Delete%20Project%20Online%20data%20using%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-858870%22%20slang%3D%22en-US%22%3EAny%20chance%20we%20can%20arrange%20a%20quick%20chat%3F%20If%20so%2C%20please%20offer%20some%20times%20(mstjohns%40gcpud.org)%20-%3F%20I%E2%80%99m%20in%20US%20PST.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859445%22%20slang%3D%22en-US%22%3ERe%3A%20Add%2C%20Edit%2C%20Delete%20Project%20Online%20data%20using%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859445%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402624%22%20target%3D%22_blank%22%3E%40MonteStJohns%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3EUnfortunately%20the%20only%20support%20I%20can%20offer%20is%20via%20this%20thread.%20You%20can%20reach%20out%20to%20one%20of%20the%20Microsoft%20Project%20Partners%20for%20consultancy%20etc.%20I%20work%20for%20a%20partner%20called%20CPS%20(CORPORATE%20PROJECT%20SOLUTIONS%20LIMITED)%20but%20you%20can%20find%20a%20partner%20here%20if%20required%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-gb%2Fsolution-providers%2Fhome%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-gb%2Fsolution-providers%2Fhome%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EMany%20thanks%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Team,

 

I am aware/comfortable accessing our Project Online data using the ODATA - I set up connections/queries using Excel 365 and Power Query etc..

 

What I'd like to do - is after loading the data from let's say the Projects object - I'd like to modify the project data values in excel - much easier than doing it in MS Project. For example, what if I wanted to change the Start and Finish Dates to be the value they are now PLUS 75 days. Easy in Excel to do a formula, then update the values.

 

I've done this with other tools for applications like SalesForce etc 

 

What I'm wondering is if/how - after I make these edits - what can I do to then PUSH the edits back into Project online.

 

Hopefully, my question makes sense?

 

 

5 Replies
Highlighted

Hello @MonteStJohns 

Copied my reply from TechNet:

It feels like you could be over engineering something here, what's the issue with project managers maintaining their project schedules in Microsoft Project / PWA? Depending on what difficulties your users are having, it might be easier to build an add-in for Microsoft Project using VBA and keep it all in Project as you then do not have any data syncing challenges https://docs.microsoft.com/en-us/office/vba/api/overview/project

Paul

Highlighted

@Paul Mather 

 

Thanks for your prompt reply.

 

At this time - I'm in the embryonic stage of trying to use PWA/PO for initially Portfolio Management - with first focus on Resource Management and Portfolio Prioritization. We don't even have enough licenses NOR have we trained PMs to use any MS Project suite of tools. 

 

So first thing I'm trying to do - is get the "single-source" of data out from 100's of separate XLS files into PWA/PO. So, me and my first EPPM team member are doing ALL the work by ourselves - and it will be that way for at least the next 90-120 days as we first have to SELL the approach, then assuming it works, then we have licensing, training etc until we're at the point we're 30-45 individuals are creating/maintaining the content.

 

Here's a link (https://www.xappex.com/#/Enabler4Excel) to the tool I've used in my past to do similar work at different organization when we were using SFDC as the primary development platform for many business process (including EPPM).

 

I'll be at the office in an hour or so and will look closely at the links you provided.

 

As always, appreciate your prompt/candid feedback.

 

Monte

 

 

 

 

 

Highlighted
Solution

Hello @MonteStJohns,

You could probably automate this import if you have consistent source data across all the Excel files but this would be custom code - potentially VBA / VSTO add-in for Excel / Project. Out of the box you do have the ability to import Excel data to Project too https://support.office.com/en-us/article/import-excel-data-into-project-cb3fb91a-ad05-4506-b0af-8aa8...

Paul

Highlighted
Any chance we can arrange a quick chat? If so, please offer some times (mstjohns@gcpud.org) -? I’m in US PST.
Highlighted

Hello @MonteStJohns,

Unfortunately the only support I can offer is via this thread. You can reach out to one of the Microsoft Project Partners for consultancy etc. I work for a partner called CPS (CORPORATE PROJECT SOLUTIONS LIMITED) but you can find a partner here if required: https://www.microsoft.com/en-gb/solution-providers/home

Many thanks

Paul