none
Access Services – Can’t Export Report Data to Excel or Word

    Question

  • ·         BOTTOMLINE: Does anyone know of any fixes or workarounds to enable Access Services to export report data to Excel or Word as detailed below?

    ·          

    PLEASE NOTE: I am talking about Access Services running under SharePoint; not client-side Access applications.

     

    When a report is rendered on the web, there is an Actions button (at bottom left hand corner) which provides the options to print the report or export the report data to .pdf, Excel or Word (see first image). Printing works and exporting to .pdf  also works just fine.  However when you try to export to Excel you receive the message shown in the second image and when you click ‘Yes’ Excel opens up but there is no data.  When you try to export to Word, the application (Word) opens up but there is nothing in the file (i.e., a blank Word document). 

     

    Based on a variety of web searches regarding this issue, it would appear that this was a known, unsolved problem way back at the end of 2010 (for example:  http://accesshosting.zendesk.com/entries/266484-exporting-report-data-to-ms-excel-or-ms-word)

     

    Does anyone know how to get the exportation of report data to Excel and Word to work?

     

    Thanks.

     

     


    • Edited by twMikeR Thursday, February 02, 2012 12:14 PM
    Wednesday, February 01, 2012 3:20 PM

Answers

  • I just thought I’d provide an update as we seem to have resolved the problem.  In our case at least, the problem was caused by permissions settings associated with the temp folders on our SharePoint Servers.  The export process apparently writes to these temp folders and therefore the ‘All Authenticated Users’ group must have modify and write permissions  to them in order to facilitate the export.  Hope this helps.
    • Marked as answer by twMikeR Thursday, July 05, 2012 6:00 PM
    Thursday, July 05, 2012 5:59 PM

All replies

  • Hi, I am also facing the same issue and googled lot but could not find any suitable solution. One workaround is to add the user to local administrator group in the SharePoint server. This will allow user to export to excel/word but this can't be treated as solution because adding any user to admin group on server may cause serious security issue.

    Please let me know in case you find any suitable solution to this issue.


    Ref: http://www.codeproject.com/Questions/200143/Access-services-export-to-excel-requires-admin
    • Edited by Vijay Js Thursday, February 02, 2012 4:18 PM
    Thursday, February 02, 2012 4:15 PM
  • I would certainly try logon to the site as a site owner, or even as a system adminsitror.

    However, I able to download all 3 formats and I am doing so without even using windows logon user names.

    Thus even non active directory user's works fine for me. I am using what is called FBA (now called claims based authentication). This CBA users are not even valid windows logons. These claims based users mean you can issue and have unlimited logons without any additional costs and without having to add users to your windows logon. ( so you don't even use the windows logon administering system here and the logon I am issuing are 100% separate from windows – such users are thus not even part of the corporate network).

    I am able with these FBA users to run reports and export of Excel and word works just fine.

    So while I don't think you need administrator privileges, I would try logon on with either site owners, or go higher up the permissions scale and try this with a server admin logon and see if that works.

    I would also ensure that Excel services are running. You don't mention if you are using your own hosted, or some external hosting here.

    However, if internal, try admin users, try some site owners. And I support try to ensure that Excel services is running.

    So while I think the advice here is great, I am not need admin or even having to use authenticated windows users for export to Excel or Word.

    I would also attempt to save the file local. Is the file really blank in size? (try a hex editor on that file – what do you see in there?).


    Last but not least, since those reports actually use SQL server reporting services, then one should check with those running reporting services on SharePoint, since this suggest they would have the same issue and thus their solutions to this issue should also work for Access services.

    Unfortunately, I cannot offer a lot more since I and many most others are not experiencing this issue.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

     

    Saturday, February 04, 2012 7:16 AM
  •  

    Here is what we’ve found.

    Excel Services is running.

    We are hosting our own site.

    We also run Reporting Services applications and the export functionality to Excel and Word DO work.

    Using site owner credentials, the export functionality does NOT work (i.e., we still cannot export to Excel or Word).

    Using SQL Server admin log-on credentials,  the export functionality DOES work (i.e., we CAN export to Excel or Word).

    So, do you have any suggestions what would allow the SQL Server admin log-on credentials to work but prevent all other credentials from working?

    Thursday, February 09, 2012 3:37 PM
  • I suspect then this question is really about running SSRS on SharePoint. And I am guessing that the same issue will occur for any report and even non Access ones? I would test/try this out since then as such this question has little relationship to Access.

    I suppose one could ask if you are running SSRS in connected mode, or local mode as that then suggests you running a separate SSRS server/system. This could very well explain why your reports work with a logon for SQL server.  In connected mode (as opposed to local mode) then SharePoint has "more" of a requirement to ensure permissions are setup to the reporting server.

    Then again I think your chances are better to hunt around for some advice in some of the SharePoint forums since I don't think your problem is limited to Access Web Services - sounds like more of a SharePoint question.

    In this group you find easy answers for using Access, but for using SSRS? It is a bit of a long shot in this group.

    However, it does sound like permisisons from SQL server are not being given to the sharePoint site correctly. So, I would check + ask around in some SharePoint groups as to this permissions issue.

    Albert D. Kallal  (Access MVP)
    Edmonton, Alberta Canada

    • Proposed as answer by Bruce Song Tuesday, February 14, 2012 8:46 AM
    • Marked as answer by Bruce Song Wednesday, February 22, 2012 9:27 AM
    • Unmarked as answer by twMikeR Monday, March 05, 2012 10:30 PM
    Saturday, February 11, 2012 1:43 AM
  • Are the extensions matched (.xls vs. .xlsx) and (.doc vs. .docx)

    or use code for different users having different versions of word and excel on their machines?


    Chris Ward

    Sunday, February 12, 2012 1:59 AM
  • I just thought I’d provide an update as we seem to have resolved the problem.  In our case at least, the problem was caused by permissions settings associated with the temp folders on our SharePoint Servers.  The export process apparently writes to these temp folders and therefore the ‘All Authenticated Users’ group must have modify and write permissions  to them in order to facilitate the export.  Hope this helps.
    • Marked as answer by twMikeR Thursday, July 05, 2012 6:00 PM
    Thursday, July 05, 2012 5:59 PM
  • This posting also gives a little more detail to twMikeR's resolution:

    https://calshare.berkeley.edu/Blog/Lists/Posts/Post.aspx?ID=11

    Thursday, February 28, 2013 5:29 AM