Update View/Fields in Dynamics CRM 2016 Excel Templates

(4) Share

Report

Posted on by Community Member

I created a new Excel Template based upon information in my current CRM environment, uploaded it, and tested it to see if it properly updates after syncing with CRM.. which it did. I had to create and add two new fields to the form that also needed to be reported on. I added the fields to the form AND to the view in which I created the Excel Template from. After exporting the template again and synching with CRM, the new fields did not appear in the table that my pivot tables/pivot charts are based on and I can't find a way to have those fields update.

So l then I attempted to create a brand new excel template and it DID show the newly added fields. However, I still need the information created on the old template (to avoid having to redo 20+ pivottables). So then I tried to copy/paste the old pivot tables/charts onto the new template and am now unable to even Refresh with CRM or anything else because the data source cannot be found.

ANY SUGGESTIONS??? ANY HELP WOULD BE HIGHLY APPRECIATED!

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (2)

Suggested answer

Community Member on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Thanks Mike, that was exactly what i was looking for. I tried it, and it worked for me

Was this reply helpful? Yes No
SolarPro 5 on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

I am finding a similar result when working in Dynamics 365 Online and Excel 2016.

I wonder if Dave was working on prem?

Was this reply helpful? Yes No
wikap.dk 456 on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Hi Dave (& other following this thread) ... can't make you suggestion work - I'm in Dynamics 365 Online and newest version of Excel (from Office365) ... do you know if this tip works today?

I'm on a customer case and would really like to modify existing Dynamics 365 Excel template the easiest way :) In this case I need to add an extra column from CRM into the existing template.

Was this reply helpful? Yes No
Monika K on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Super helpful, Mike Wan!

Been looking for this solution for a bit.

I successfully used your solution steps together with "hiddenDataSheet" update as the fifth step, followed by a save of the new template locally and its upload to Dynamics org.

Was this reply helpful? Yes No
Suggested answer

DaveDB 5 on at

Like (1)

Report
RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

I ran into a similar issue the other day and I figured out a solution.

It turns out that Excel Templates have a sheet named "hiddenDataSheet" that contains the mapping from the CRM view columns to the Excel table column names. This sheet is "very hidden" so you need to use "View Code" to unhide it, see the following for details:

http://www.excel-university.com/make-an-excel-worksheet-very-hidden/

If you make the "hiddenDataSheet" sheet visible, you can find the column mapping in cell A1. The format of the mapping should be pretty self-explanatory, but essentially you would add something like this, escaping spaces as necessary:

&<CRM view column name>=<excel column name>

Once you've added your new columns to this mapping, you should be able to re-hide the "hiddenDataSheet" sheet and upload your new template.

Hope this helps!

Was this reply helpful? Yes No
Alejandro Cesetti 205 on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Hi Mike,

I have followed your steps (or I guess so) and even when I added the columns with the 2 new fields (instead of one, I had to add two), I see them available in the Pivot Table but they are not populated with data when downloading the new spreadsheet.

Can you add a couple of screenshots showing how you create the new column, copy and paste the new column?

Thank you very much for your time.

Was this reply helpful? Yes No
Suggested answer

ImAPilot 70 on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

I was struggling with the same issue using Word Templates where I needed to include newly added custom fields, and found a similar solution that worked for me. Simply generate the new template containing the new fields, Insert the field that you want to add in the new template. Then copy the field from the new template to the old template.

Sadly, the XML Schema does not get updated with the new field to make it easier to edit in the future, but it does beat copying all of your document and formatting from the old template to the new template.

Was this reply helpful? Yes No
Verified answer

Mike Wan 190 on at

Like (3)

Report
RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

I met similar issue today and when I search from the web, I found out your post.
I was trying to add a new field to the uploaded excel template but the pivot chart can't identify it.
After played for several hours, I finally got a working way.

Include the new field which you want to add to a brand new excel template and download it. (don't need to care about if the view is the same as before, just make sure the needed column is included)

Also download the template you want to update.

Open these two files side by side, and then copy the new column you need and "insert" it into the old version of template. (The key point is how to insert it. Make sure first of all, insert a blank column in the middle of old version of template, and then copy the new column and paste it into the column you inserted just now)

"Refresh Data" from the pivot table or pivot chart. Observe if this newly pasted column can be found from the right hand side field list.

if you can, update the pivot chart with it and then upload this updated template back to the CRM with a new name. It should be also working fine within Excel Online, at least it is from my side.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

That's yet another issue I've found. Your tables and charts will blank out upon opening from CRM if you don't:

A) Create a brand new template before you copy/paste old info into the new one and edit the Data Source

B) Use the EXACT same view (just updated) to export/download that new template

I will try to create a document with screenshots on all of the workarounds when I have time and will post the link to it here.

Was this reply helpful? Yes No
rpa_senthil on at

Like (0)

Report

RE: Update View/Fields in Dynamics CRM 2016 Excel Templates

Hi, I tried your steps, but as soon I did the "select every single pivot table one-by-one and go to the Analyze tab > Change Data Source > Type in Table1", my Pivot table got blank and the Charts too. if possible, could you share the step by step screenshot. I just have 4 pivots, but have lot of slicers with colorful formatting, it would take hours or even days to recreate them. Thanks in advance.

Was this reply helpful? Yes No