locked
dynamic sheet export to Excel 2003 RRS feed

  • Question

  • With 2007 the sheet just comes up and asks if it should import the data.  Of course, I'm here inside the firewall.

    However, I have a user who has Office 2003.  When she does the same thing remotely to CRM 4.0 (and static sheets work fine) she get a spreadsheet asking for username and password.  So I guess the first question is, having filled in the name and password cells, how does one say 'OK' to continue the loading process?
    Tuesday, April 14, 2009 9:41 PM

Answers

  • The reason  you can open a static spreadsheet without the Outlook client is that a static spreadsheet is generated server-side and then downloaded by the client machine - the downloaded file already has all the data in it, essentially as an HTML table with a .xls extension on it.  But with a dynamic worksheet, the file downloaded and opened by the user is only a pointer to the query, and Excel executes the query subsequent to opening the file, downloading the data at that time.  Outside the domain (with IFD authentication), it's the Outlook client that provides interoperability between Office and CRM - it handles authentication for you.  Without the Outlook CRM Addin running, Office can't authenticate to CRM.  This should be consistent in 2003 and 2007. 

    See Joel's blog on this topic:
    http://blog.customereffective.com/blog/2008/05/dynamic-workshe.html
    Ryan | Zero2Ten | http://www.zero2tencrm.com/
    • Marked as answer by Jim Glass Jr Thursday, April 23, 2009 8:34 PM
    Thursday, April 23, 2009 8:17 PM

All replies

  • Check to see if there is a button on the top menu to Enable Data Connections.  Once the user enables the Data Connection the data should load.  With Office 2003 the security warning that Data Connections are disabled might be slightly different.  It has been a while since I used 2003 so I don't recall the exact place to Enable.  The feature might be found under Data in the top menu.


    Best Regards, Donna
    Wednesday, April 15, 2009 1:41 PM
  • You probably need to install the CRM Outlook client on the remote computer and login with a VPN to the server. You could also consider a IFD for remote computers that are not part of your domain.

    Thursday, April 16, 2009 2:41 PM
  • Is she seeing the user name and password prompts inside the spreadsheet itself rather than a popup?  If so, she shouldn't enter user name and password, it means the connection to the CRM server isn't established yet.

    If she's connecting through integrated authentication it should just automatically authenticate, but as Donna mentions, she may need to enable the data connection.  If she's connecting throught IFD rather than on the LAN and on the domain, she must additionally have the Outlook CRM client open before launching the spreadsheet and still will need to enable the data connection once the spreadsheet opens.

    For IFD connections, Outlook serves as the connection middleman between Excel and CRM.


    Ryan | Zero2Ten | http://www.zero2tencrm.com/
    Friday, April 17, 2009 4:28 PM
  • Yes, on the spreadsheet itself.

    I'm not sold on your answer as she has NO Outlook client at all.  And has no problem exporting static spreadsheets.  This is not some subsequent opening of the spreadsheet.  This is the initial opening/export.  It appears the IFD connection via IE is enough to have a connection to get the data.

    I think it's an Excel 2003 where's-the-button question -- where's the enable connection button on 2003?  I will, however, test this using 2007 once I get home, outside of the domain.

    Friday, April 17, 2009 4:51 PM
  • what happens if she saves the file to her harddrive after exporting and before opening, and then goes to the file and opens it?  Can you provide a screenshot of the spreadsheet that contains the prompt for user name and password, that might help.
    Best Regards, Donna
    Friday, April 17, 2009 4:56 PM
  • I will let you know when I find out.  Can I paste snapshots in here?
    Friday, April 17, 2009 6:31 PM
  • you can paste a link to them or format the html code to display the image rather than the link


    Best Regards, Donna
    Saturday, April 18, 2009 3:38 PM
  • She says it doesn't matter if the sheet is save or opened immediately.  What she sees looks something like this:

    <table>
    <tr>
      <td><b>Sign In</b></td><td></td>
    </tr>
    <tr>
      <td><b></b></td><td></td>
    </tr>
    <tr>
      <td><b>Username:</b></td><td></td>
    </tr>
    <tr>
      <td><b>Password:</b></td><td></td>
    </tr>
    <tr>
      <td><b></b></td><td>Sign In</td>
    </tr>
    <tr>
      <td><b></b></td><td>Forget your password?</td>
    </tr>
    <tr>
      <td><b></b></td><td>Contact your Microsoft Dynamics CRM administrator</td>
    </tr>
    </table>
    Thursday, April 23, 2009 6:46 PM
  • Have your user right-click in the area where the records should show up and choose Edit Query.

    The login window should display, where your user can enter her credentials.  Once logged in, she will receive an error, but after closing the Edit Query window, she should be able to refresh the data area and see the expected records.

    It's a sloppy workaround, but it is a workaround.
    Thursday, April 23, 2009 7:10 PM
  • The reason  you can open a static spreadsheet without the Outlook client is that a static spreadsheet is generated server-side and then downloaded by the client machine - the downloaded file already has all the data in it, essentially as an HTML table with a .xls extension on it.  But with a dynamic worksheet, the file downloaded and opened by the user is only a pointer to the query, and Excel executes the query subsequent to opening the file, downloading the data at that time.  Outside the domain (with IFD authentication), it's the Outlook client that provides interoperability between Office and CRM - it handles authentication for you.  Without the Outlook CRM Addin running, Office can't authenticate to CRM.  This should be consistent in 2003 and 2007. 

    See Joel's blog on this topic:
    http://blog.customereffective.com/blog/2008/05/dynamic-workshe.html
    Ryan | Zero2Ten | http://www.zero2tencrm.com/
    • Marked as answer by Jim Glass Jr Thursday, April 23, 2009 8:34 PM
    Thursday, April 23, 2009 8:17 PM