Question Status

Verified
Sravan Kumar Ch asked a question on 16 Mar 2013 2:06 PM

When I export to Excel as dynamic work sheet, i don't see any data coming. any pointers please

Reply
Verified Answer
Muhammad Adeel Javaid responded on 16 Mar 2013 2:28 PM

On a default Microsoft Dynamics CRM installation, before you export data to an Excel dynamic worksheet, make sure that:

Microsoft SQL Server server allows remote connections.

Windows Firewall allows remote SQL Server connections. See How to: Configure a Windows Firewall for Database Engine Access.

In any area with a list of records, on the <Record Type> tab, in the Share Data group, click the Export to Excel.

Click Dynamic worksheet.

By default, an exported worksheet includes the fields that are displayed in the list, using the same field order, sorting, and field widths.

Click OK, and then click Export.

view the file containing the dynamic worksheet:

i)- To view the worksheet, click Open.

ii)- If you are using Microsoft Office Excel 2007:

a)- You will see a message that says the file that you are trying to open is in a different format than specified by the file extension. Click Yes.

b)- If you see the security warning Data connections have been disabled, click Options, click Enable this content, and then click OK.

iii)- If you are using Microsoft Excel 2010:

a)- You will see a message that says the file that you are trying to open is in a different format than specified by the file extension. Click Yes.

b)- You will see a message that says that the file was opened from a potentially unsafe location. Click Enable Editing.

c)- If you see the security warning Data connections have been disabled, click Enable Content, and then click OK.

To refresh data in the file, on the Data tab in the Connections group, click Refresh All.

To save the exported data to a file, in Excel, on the File menu, click Save.

Note: Each time you open the file, you have the option to refresh data from the Microsoft Dynamics CRM database.

M.Adeel

Reply
Suggested Answer
Frank Lee responded on 16 Mar 2013 4:03 PM

Check that you have the CRM Outlook Client installed on the same PC that is running the Dynamics Excel worksheet

Frank Lee
CRM MVP since 2006
Blog
Twitter

Reply
Sravan Kumar Ch responded on 16 Mar 2013 4:29 PM

We have two CRM clusters. Dev  and Test.

1. Dev has CRM instance with SQL -2008

2. Test has CRM instance with SQL-2005.

Below solution worked on 2008 but didn't profit on 2005. Any help would be greatly appreciated. Thanks

Reply
Muhammad Adeel Javaid responded on 16 Mar 2013 4:53 PM

Did you check profile permissions? What is the exact error you are getting.

Regards

M.Adeel

Reply
Sravan Kumar Ch responded on 18 Mar 2013 3:17 AM

I dont receive any error. but upon opening the excel file, i have  user and password in 2 cells. Screen shot below.  Thnaks in advance.

 

Reply
Muhammad Adeel Javaid responded on 18 Mar 2013 3:47 AM

Check from your CRM Settings if the password protection option is set to ON. This usually happens when you set the password protection to ON mode for content deliveries.

M.Adeel

Reply
Sravan Kumar Ch responded on 18 Mar 2013 9:56 AM

Hi Adeel,

Could you tell me where this setting is present. I am not aware of the above password protection setting in CRM. Thanks.

Reply
Muhammad Adeel Javaid responded on 18 Mar 2013 12:04 PM

You have to find the user under the “All People” page, then edit him by clicking on the name. On the right side, under the “Special Actions” metabox, click on “Toggle Settings” and you will find the fields to set the password.

M.Adeel

Reply
Muhammad Adeel Javaid responded on 18 Mar 2013 12:09 PM

I am copying a link below. It may also be helpful for you.

social.technet.microsoft.com/.../11298.microsoft-dynamics-crm-2011-dynamic-worksheets-in-excel-feature.aspx

M.Adeel

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 18 Mar 2013 12:11 PM

Dynamic Worksheets and/or Dynamics Pivot Tables require the use of the Excel addin which is part of the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook client. You must install the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook found here:

www.microsoft.com/.../details.aspx

M.Adeel

Reply
Gustaf Westerlund responded on 18 Mar 2013 3:21 PM

Hi,

If you are running traditional AD On-Premise, you do not need the Outlook client. However, there is a very strange behavior of the SQL-server as it for some strange reason communincates on epipherimal port (very high typically outgoing ports) instead of the normal SQL port. The port used seems to be randomized based on the SQL installation. Do not ask me why. However, I have blogged about this and how to find out using Netstat. Do read about it here: gustafwesterlund.blogspot.se/.../finding-tcp-port-for-dynamic-excel.html

Gustaf

Regards Gustaf Westerlund
MVP, CEO and Chief Architect at CRM-Konsulterna
www.crmkonsulterna.se
CRM Blog

If you find answers useful, please mark them as answered, it will help all of us in the community!

Reply
Sravan Kumar Ch responded on 19 Mar 2013 4:00 AM

I don't see any such option as "Special Actions" on user profile pages. Could you insert a screen shot for me. Thanks in advance.

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 19 Mar 2013 11:38 AM

Please follow the link I am copying below to learn about Excel Security Settings:

www.todosobrecrm.com/.../excel-2010-and-data-import-templates-2

Hope this will work for you.

M.Adeel

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 19 Mar 2013 11:39 AM

Change the setting in Excel to allow it to open the files in normal mode for all files originated from Internet.

a) Go to File->options.

b) Go to Trust center ->Trust Center Settings.

c) Go to Protected view.

d) Uncheck the Settings “Enable Protected view for Files Originating from internet”.

Another option is to choose Save instead of open.  Then save it to the desktop then open it.   This means that Excel is opening it from a local Computer and not the internet.

M.Adeel

Reply
Anne Stanton responded on 19 Mar 2013 3:57 PM

Muhammad has a great summary - but you also want to check your security.

Anne

Anne

aka The CRMLady at http://www.crmlady.com 

Reply
Verified Answer
Muhammad Adeel Javaid responded on 16 Mar 2013 2:28 PM

On a default Microsoft Dynamics CRM installation, before you export data to an Excel dynamic worksheet, make sure that:

Microsoft SQL Server server allows remote connections.

Windows Firewall allows remote SQL Server connections. See How to: Configure a Windows Firewall for Database Engine Access.

In any area with a list of records, on the <Record Type> tab, in the Share Data group, click the Export to Excel.

Click Dynamic worksheet.

By default, an exported worksheet includes the fields that are displayed in the list, using the same field order, sorting, and field widths.

Click OK, and then click Export.

view the file containing the dynamic worksheet:

i)- To view the worksheet, click Open.

ii)- If you are using Microsoft Office Excel 2007:

a)- You will see a message that says the file that you are trying to open is in a different format than specified by the file extension. Click Yes.

b)- If you see the security warning Data connections have been disabled, click Options, click Enable this content, and then click OK.

iii)- If you are using Microsoft Excel 2010:

a)- You will see a message that says the file that you are trying to open is in a different format than specified by the file extension. Click Yes.

b)- You will see a message that says that the file was opened from a potentially unsafe location. Click Enable Editing.

c)- If you see the security warning Data connections have been disabled, click Enable Content, and then click OK.

To refresh data in the file, on the Data tab in the Connections group, click Refresh All.

To save the exported data to a file, in Excel, on the File menu, click Save.

Note: Each time you open the file, you have the option to refresh data from the Microsoft Dynamics CRM database.

M.Adeel

Reply
Suggested Answer
Frank Lee responded on 16 Mar 2013 4:03 PM

Check that you have the CRM Outlook Client installed on the same PC that is running the Dynamics Excel worksheet

Frank Lee
CRM MVP since 2006
Blog
Twitter

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 18 Mar 2013 12:11 PM

Dynamic Worksheets and/or Dynamics Pivot Tables require the use of the Excel addin which is part of the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook client. You must install the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook found here:

www.microsoft.com/.../details.aspx

M.Adeel

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 19 Mar 2013 11:38 AM

Please follow the link I am copying below to learn about Excel Security Settings:

www.todosobrecrm.com/.../excel-2010-and-data-import-templates-2

Hope this will work for you.

M.Adeel

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 19 Mar 2013 11:39 AM

Change the setting in Excel to allow it to open the files in normal mode for all files originated from Internet.

a) Go to File->options.

b) Go to Trust center ->Trust Center Settings.

c) Go to Protected view.

d) Uncheck the Settings “Enable Protected view for Files Originating from internet”.

Another option is to choose Save instead of open.  Then save it to the desktop then open it.   This means that Excel is opening it from a local Computer and not the internet.

M.Adeel

Reply
Suggested Answer
Anne Stanton responded on 27 Mar 2013 9:36 AM

It could be that EXCEL Macros are disabled by your anti-virus software. Make sure that Excel Macros are enabled.

Anne

aka The CRMLady at http://www.crmlady.com 

Reply
Suggested Answer
Gustaf Westerlund responded on 27 Mar 2013 1:58 PM

Have  you tried running NetStat when Excel tries to get the data to see if there is and handshakes that are not being successful?

Regards Gustaf Westerlund
MVP, CEO and Chief Architect at CRM-Konsulterna
www.crmkonsulterna.se
CRM Blog

If you find answers useful, please mark them as answered, it will help all of us in the community!

Reply
Suggested Answer
Muhammad Adeel Javaid responded on 27 Mar 2013 7:53 PM

After exporting your Dynamic Data and Opening it in Excel, please click the Refersh from CRM button in the Ribbon.    Dynamic Export doesn't actually download any data, instead it  saves the connection string information to an Excel File so that you can load the data directly in Excel.

When you click the Refresh from CRM Button it then queries your CRM Server, and retrieve your current data.

M.Adeel

Reply
Suggested Answer