web
You’re offline. This is a read only version of the page.
close
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) / Automatically Format E...
Microsoft Dynamics CRM (Archived)

Automatically Format Export to Excel

(0) ShareShare
ReportReport
Posted on by Community Member

Hey all,

I received an email from my primary stakeholder:

"Can you preformat an Excel Extract from CRM?  Something as simple as defining the width of the columns. Already got complaints that the extract looks different from the master summary sheet I was updating manually before CRM.  Figured I’d check. Thanks."

Nobody enjoys formatting data, so I figured that this would be a potential issue. Basically, when exporting, there is no automatic word wrap or formatting. I was curious of the best practice, easiest way for an end user to do this. The only ways I can imagine this is by:

  1. Creating a custom report displaying the info in their view
  2. Providing them with an Excel template to cut and paste into every time they export
  3. Create a Macro that would apply the template when selected.
  4. I can’t find anything online for creating an Export to Excel template – would there be potential to customize that into the sitemap? Strange thought since I don’t know much about the sitemap customization and extensibility.

I would tell them...

  1. Click A1 (actually D1 when exporting from CRM)
  2. Ctrl+Shift+(Down Arrow)
  3. Ctrl+Shift+(Right Arrow)
  4. Alt > H > W
  5. Alt > H > O > I
  6. Alt > H > O > A
  7. Alt > H > AT
  8. Alt > H > AL

...but nobody understands that that could take 10 seconds :(

Thanks for any insight!

Best,

Blayze

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Drew Poggemann Profile Picture
    Drew Poggemann 4 on at

    Hi Blayze,

    Maybe go after this in a reverse order? Setup a Spreadsheet that connect to your CRM data source via OData, or sql if on premise, and setup the data Sheet to refresh appropriately. This way you can control the format of your spreadsheet and you are just "refreshing" the data from CRM any time you want to run the report.

    Thanks much,

  • Community Member Profile Picture
    Community Member on at

    Hi Drew,

    I appreciate your response. Great theory. I tried both Odata, as well as just exporting as a dynamics worksheet. I think I am missing how to "control the format", like you said. Each time the data is refreshed (tested automatic refresh when opening the file, auto-refreshing every minute,  as well as by selecting the "Refresh" button), the formatting goes back to the original export format.

    In the interim, I will be providing a link to my stakeholders on our SharePoint which will refresh upon opening, and inform them of the keystroke for my macro.

    Providing these two links which I followed that may help anyone else:

    survivingcrm.com/.../connecting-crm-online-odata-feed-excel-2013-power-query

    m.youtube.com/watch

    Do you know how to keep the formatting when refreshing the data?

    Best,

    Blayze

  • Drew Poggemann Profile Picture
    Drew Poggemann 4 on at

    Hi Blayze,

    We have actually had to do something somewhat similar before but we were refreshing from a database source vs. from CRM source but same concept.  What we did is have the data as a separate worksheet in the workbook and utilized VBA code to bring this information into other worksheets to output of the report information required.  We had probably 8 worksheets in the workbook that all created reports from the same source data.  

    Steps we used that should work:

    1.  Worksheet will exist that is refreshed from CRM

    2.  Another worksheet will be setup that is formatted the way you want where the data will be displayed in the report format you need

    3.  Create an ActiveX control that will refresh the data in your formatted sheet from the data in the CRM sheet.  This will utilize VBA code to clear the data and then loop through all the rows in the source CRM worksheet and populate into the target worksheet.

    4.  If you need to do additional formatting after the data populates you can use Excel formatting commands in VBA code to do that as well (we had to do this to format totals, etc.)

    If you would like some "example" code I can get you some, let me know.

    Hopefully this helps.

    Thanks,

  • Community Member Profile Picture
    Community Member on at

    Thanks for the help. If you are willing to give the example code, I would appreciate it - thanks!

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans