Email : info@epmstrategy.com | Call Us Now : 479-321-3977
:: Home :: Sitemap ::
Follow Us

Connecting Excel 2013 to Project Online using Odata

Connecting Excel 2013 to Project Online using Odata

By Collin Quiring

We have seen issues for some folks when connecting Excel 2013 to Project Online using an Odata connection.  This blog is to give an example of how to get data from that connection without ever connecting to Project Online directly.

Start by opening Excel 2013 and going to the DATA tab.  Then, select From Other Sources in the ribbon.  On the drop-down, select From Odata Data Feed.

 

Connecting1

This will bring a Data Connection Wizard popup.  Put in the Link to your Project Online site followed by

/_api/ProjectData/

THIS IS CRITICAL.  So, in our sample, with our Project Online site being at “https://PMPSpecialists.com/sites/Demo7PWA”  the link to enter in the Link option is:  “https://PMPSpecialists.com/sites/Demo7PWA/_api/ProjectData/ “  You may or may not need to enter a sign-in at this point, in this example, we do not need one.

 

Connecting2

 

 

 

 

 

 

 

 

NOTEBe careful when copy/pasting the URL link.  We have seen where the link will duplicate in the Link of File box (it is hidden at first so we recommend you place your cursor at the far left of the box and do NOT select the entire box but use your arrow key and scroll over to confirm that the URL is only in place one time).

If the URL (URI) is incorrect, or you don’t have permissions,  you may get any of the following error messages:

 

Connecting3

(We couldn’t get data from the Data Model. Here’s the error message we got:

Invalid URI: The URI Scheme is not valid.)

Connecting4

(Unable to obtain list of tables from the data source.)

Connecting5

(We couldn’t get data from the Data Model.  Here’s the error message we got:

The remote server returned an error: (400) Bad Request.)

Connecting6

(We couldn’t access the data feed server because the URL is invalid.)

Once you have connected, after entering the link in the Data Connection Wizard, the second popup titled Data Connection Wizard with the option to Select Tables will appear.

Connecting8

 

 

For our example, we scrolled down and selected Projects as the table to bring back.

Connecting9

 

 

The third box for the Data Connection Wizard appears that allows us to Save our Data Connection.  Note – we are not saving the results, we are saving the .odc file only.  Results are saved from Excel as you would normally save any file.

 

Connecting10

 

 

gives us the option of which type of Excel workbook we want (Table, Pivot, etc.).  And, we can select the Existing Worksheet or a New Worksheet.

Clicking OK brings back the Excel file and the data.

 

Connecting11

 

 

 

 

 

 

 

And, here is a small piece of one item from the Project file in a normal worksheet from our Project Online instance:

 

Connecting12

And, as an example, the Data Source we saved earlier, is now sitting on our PC for us to be able to use in the future.  The default location is “This PC / Documents / My Data Sources”

 

Connecting13

 

As further proof that the Data Connection now exists, the next time you want to use it, Select the DATA tab from Excel, the Existing Connection option in the ribbon and the data connection you created will be there for you to use!

 

Connecting14

 

 

Comments

One Response to “ Connecting Excel 2013 to Project Online using Odata ”

  1. […] Before continuing with this post, please read how to create an OData connection to your Project Online instance:  Create OData Connection […]

 

Leave a Reply

You must be logged in to post a comment.