Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Can a dynamic spreadsh...
Microsoft Dynamics CRM (Archived)

Can a dynamic spreadsheet from CRM automatically be refreshed?

(0) ShareShare
ReportReport
Posted on by JennyW

We use CRM 2015 Online.  I created a dynamic spreadsheet from CRM data.  I have saved it in a public folder on our network so other employees can access the report data.

My question is this:   Is it possible to have the spreadsheet automatically refresh itself so that when others (including some non CRM users) access it, it has up to date data? 

Under Connection Properties in Excel, I have tried setting the "refresh every" to a few minutes to see if it works but it doesn't.   I also tried selecting "Refresh data when opening the file" but that doesn't seem to work either.  It replaces the information with blank cells.

The only time the data is refreshed is when I manually select "Refresh from CRM".   I am hoping to find a method that will avoid any manual refreshing and be able to set it on a weekly timer.

Any help would be greatly appreciated!

*This post is locked for comments

  • Isabelle D365 Profile Picture
    Isabelle D365 5 on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    Hi Scott

    Are you able to share the macro? Thanks!

  • Suggested answer
    ScottbudNZ Profile Picture
    ScottbudNZ 35 on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    I Had the same issue. I created a separate workbook with a macro to open the desired workbook, wait 2 minutes, save and exit. 

    Then I used windows scheduler to run the macro workbook. 

    Not the most elegant but it works

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    Nuno, I very much appreciate you taking a look at this and responding.  It's beyond me technically but I am sending it to my CRM developers to see if they can recreate it for us.  If you are able to find an alternative approach I, and I'm sure others, would love to hear it.

    Best,

    Sean

  • Suggested answer
    nplima Profile Picture
    nplima 540 on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    Hi

    After some experimenting and testing, I found a workaround which means rebuilding the reports rather than a simple export as before. Possibly this is related to network security and/or the Outlook Client being replaced with a different sort of application.

    You'll need a username/password from a Dynamics 365 user to have Excel load the data regularly to the PC that is feeding those monitors.

    From Excel, open the 'Data tab', open 'New Query', choose 'From other source', 'from Dynamics 365 Online'. Excel will need a "Web API URL", which you get from the Dynamics 365 'Settings/Customisations/Developer Resources' screen.

    Now what follows is that you need to build your reports in Excel. The window that pops up lets you pick the entities in Dynamics where you will get data. The Edit button lets you make filtering and pre-processing before the data goes into Excel.

    This is essentially the same process you'd follow to build the report in Power BI, so we're talking about creating new columns, replacing GUIDs and data values with data labels, etc. You might do a lot better by picking a Power BI template or report set and build your status screens from there!

    When you are ready, use the "Load TO" button rather than "Close and Load". This will give you the option to load the data to the Excel data model rather than dump data into a table. This makes it easier to build and format your reports using the Pivot Table and Pivot Chart tools.

    When done with the formatting, the Data tab will have a 'Refresh all' button, which you can expand to access 'Connection properties'. This is where you set the refresh interval. Save the file and you're done.

    I tested this only very briefly and I find it works but is too complicated. Sharing the completed file requires the end user to authenticate in Excel with a Dynamics user/pwd.

    Next, I'll try to do the same with Excel templates in Dynamics 365, I think it might be a better way forward.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    We are having the same problem since we updated to Dynamics 365 from Dynamics 2011 in January.  We use auto-refresh to display work-on-hand information on monitors throughout the department and had no problems in the prior version.  There is a work around but it needs to be done every 24 hours.   It has been a point of frustration that we have been unable to use this feature in the same way since the upgrade.   Any solutions would be very welcome.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    I am facing the same issue and i even have CRM outlook plug-in installed. Any suggestions?

  • Suggested answer
    Balpreetm Profile Picture
    Balpreetm 20 on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    This will only work on your local machine, you will need microsoft crm outlook installed(Add-On) as mentioned by Alex. Below will give you more information

    www.microsoft.com/.../export-to-an-excel-dynamic-worksheet.aspx

  • Alexander BB Profile Picture
    Alexander BB 295 on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    Any user that wants to use your Dynamic Excel Sheet must have a working CRM Outlook plugin enabled and connected to that CRM environment. Also they have to have a CRM role with at least read access to the wanted data.

  • Eric benco Profile Picture
    Eric benco 320 on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    I'm seeing the same thing, blank worksheet if the 'Refresh when opening the file' option is set.   Clicking "Refresh All" doesn't bring it back,  I have to explicitly click "Refresh from CRM"   I'm using Office 16.0.6965.2115  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Can a dynamic spreadsheet from CRM automatically be refreshed?

    Same issue here, all blanks after refresh doesn't matter the setting.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,321 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans