Introducing a new feature in Access 2013 web apps—Download in Excel

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

It’s just the nature of shipping software in a services world with Office 365—when we hear your feedback, we can respond with new features that fill your needs. In the case of Access 2013 web apps, one of the most frequent feature requests we heard from customers was the need for an easier way to view their web app data in Excel. Currently, the process for viewing your Access web app data within Excel involves many steps.

We’re proud to announce that a new feature called, “Download in Excel” is available to all of our Access 2013 Office 365 customers, which makes this process much simpler. The changes are on the server side which means you do not need to update your Access client program at all. This update is available for customers using Office 365 with Access Services so look for this feature soon within your web apps and try it out.

How does the Download in Excel feature work?

When viewing any datasheet views in your browser for your Access web app on Office 365, you’ll see the new Download in Excel button on the right side of the Action Bar, as shown in the following screenshot:

Download in Excel 1

When you click the Download in Excel button, you are prompted to either open or save the spreadsheet. If you choose to open the spreadsheet, Excel downloads a copy of the data from the datasheet view and then immediately displays it within the familiar Excel interface, as shown in the following example:

Download in Excel 2

Once you have the data in Excel, you can utilize the rich set of Excel features to perform different tasks such as:

Currently, you can download up to 10,000 records from a datasheet into Excel. If you do not want to show this option for specific datasheet views, you can hide the Action Bar for that view, which will hide the Download in Excel button.

We hope you enjoy this new Download in Excel feature within Access web apps for SharePoint, which opens up more scenarios for your custom web apps built on top of Office 365. Give it a try and let us know what you think!

  1. Thank you, I have been looking forward to this feature for a long time. Just checked Microsoft’s Computer Asset Management System app from the SharePoint store (I downloaded when it first came out), and the Download to Excel button does not appear. Will the app be updated to include this feature automatically, or is this only for new apps that I create?

    • Once the feature is enable (soon!), the button will appear for any datasheet view that has the action bar visible, including all existing apps.

  2. I’m not sure that it is a good idea to have to hide the entire Action Bar in order to hide the Download in Excel button though. In order to stop people downloading lots of information into Excel and taking it away on a USB stick, we now have to hide the Delete and Add buttons on each datasheet?

    • That is the way that the feature is designed for its initial release. We do appreciate the feedback, and hopefully can incorporate it a future iteration. Also, note that although it makes it more convenient, the new feature does not expose any data that a user could not previously get to.

  3. The blog post says, “Currently, you can download up to 10,000 records from a datasheet into Excel. If you do not want to show this option for specific datasheet views, you can hide the Action Bar for that view, which will hide the Download in Excel button.” Is it not possible to delete the ‘Download in Excel’ button if we want to do that?

    Also, is it possible to create our own button which downloads into Excel? For that it would need a macro action, presumably.

    • It is not possible in the current implementation to hide or delete the Download button, except by hiding the action bar.
      You are correct, we did not expose a macro action, so you cannot do this with a custom button, but thank you for the suggestion.

      • @Shane,
        So the bit in Jeff Conrad’s excellent “Microsoft Access 2103 Inside Out” which says,

        “You can dynamically change the visibility and enabled state of any of the built-in Action Bar buttons on your views using the SetProperty action. For example, you might find this useful to selectively hide or show the buttons based on conditions within the view. To reference the built-in Action Bar buttons in the SetProperty action, use addActionBarButton, deleteActionBarButton, editActionBarButton, saveActionBarButton, or cancelActionBarButton for the Control Name argument. You can use Enabled or Visible for the Property argument.”

        won’t apply to the Download to Excel button initially?

        • That’s correct, Alan. The button is not technically an action bar button. We thought the placement would make sense, but it has differences in behavior (such as appearing on all existing datasheet views). It is separated visually from the other buttons using a separator partly to indicate that it is not a ‘normal’ action bar button.

        • I’m finding that attempting to set the property of the inbuilt action buttons in datasheet view throws an error 3270. Works OK for List View just not Datasheet view.

          Anyone else have success doing this?

          • Kent, can you clarify what you are trying to do?
            What property are you trying to set? The inbuilt action bar buttons don’t have any user-settable properties. Unless you mean you are adding a custom action bar button. In which case you can set a name, icon, tooltip, and of course an OnClick macro.
            That should certainly work for custom buttons, regardless of which view.
            Does this question relate to the Download to Excel feature being announced here?

  4. Hi folks, thanks for the questions. We’re glad this feature will be useful.

    @scooter – Yes, your apps will update automatically. We’ll update this blog post when the feature is live!

    @Alan – Thank you for the feedback. At this time, to hide the button, you will need to hide the action bar. Note, even with the action bar hidden, this will not stop a user from being able to download through copy/paste.

    Also, this action is not available to be called via a macro command. Is there a particular scenario you had in mind?

    • @kepatel. In some of my “classic” Access apps, I have scenarios where users export data to Excel without first seeing it all in Access, so they might want to do the same in a web app.

      On the copy/paste front, are you correct.Even if I set all the textboxes in a datasheet to not be enabled, the user can select the record selectors and copy the entire record(s).

  5. Hi,
    very useful for our team reporting as we’re using the Access-based Project Management app to track our projects.
    Question: will that function automtically create a refreshable data connection in the Excel export sheet (like is done in owssvr.iqy when exporting to Excel from a SharePoint list) ?

    • No, the initial implementation of this feature just provides a snapshot of the data. To get current data, you will have to go to the app and push the Download button again. If you want to build a workbook that does analysis that you want to repeat on updated data, the easiest route is probably just to download again, then copy from the new workbook, and paste it into your existing one. We’ll keep refreshable connections on the list of things we will consider for future iterations.

      • @Shane, We could also download the Excel data to a sort of “backend” spreadsheet, link a front end spreasheet to it and keep all your calculations and stuff in that front end sheet.

      • I would not suggest refreshable connections due to security implications, as I presume the connection string will have to use the SQL Azure db connection which effectively bypasses SP user security. You definitely don’t want errant workbooks floating about that have direct connections into the db. It’s much better for the developer to build the connection directly into the workbook and have the workbook secured in SP where it can be viewed and refreshed without letting the user download a local copy.

        PS. Love the new feature. I agree with the general consensus that the ‘download’ charm should behave like the rest of the action bar buttons.

        • Thanks Ryan. If we do implement a refreshable connection, it won’t contain a connection string for the SQL Azure db.

  6. Great news. We have been using an Access web-app to do stock management and I have been looking for a simple Excel export.

  7. Interesting feature. I have the following remarks though: –

    1. An Access datasheet view is going to read (or read/write) either from a table or from a query. and this is exactly what List view does. It does not make a sense therefore to have this Download to Excel button available only in datasheet views. it should also be available for List views!

    2. We should be able to choose which columns to download to excel. How data is presented in Access when a table (a datasheet view) has linked tables is a lot different from when downloaded in excel, I guess looked up columns will show looked up IDs not display values, a data I like to exclude from my download.

    • 1. We did consider this, but decided not to include it in our initial release. The datasheet is more natural in the sense that the data is already in a columnar layout. If the feature is launched from list view, then we would have to translate the layout of the view into a columnar form, for example.

      2. Again, we tried to keep the initial release simple. We will however, export the display values for lookups, not the bound values (so user’s won’t see the IDs).

      • This feature has started to work in my apps. Basic but I hope it will be further developed. For the time being, I will still prefer reporting via Power Query.

  8. Thanks for this good feature. Just what I needed. But may I ask if I am still using a trial version, will this update be applied to my Access web app on share point too? Thanks

    • Yes, this feature should still be available in the trial version as well.
      Keyur Patel

    • Hey Alan, we appreciate the enthusiasm! The rollout is in process, so it should start showing up in some production tenants within a few days. I am not directly involved in that process, but if I understand the way that First Release works, it should indeed show up for those who opted in first. Let us know when you see it (or if you don’t see it within a few days).

  9. Where would be the best place to feature request in Access web apps? Although I am subscriber to Microsoft Office 365 Network at Yammer, Access IT Pro Group is the least active amongst all groups.

  10. Hi thanks .
    This function is very usuful for us .
    We did it to copy and paste to Excel sheets , but we couldn’t finc field name etc .
    So it was very inconvenient .
    Woderful !!!!
    sent from Tokyo , Japan .

    • There are a couple of ways of having field names. One is to only use the downloaded spreadsheet as a sort of “back end” file. If you create a “front end” spreadsheet whose cells are all linked to the downloaded spreadsheet, you can manually add a first row with your field names in. You can also add further columns with calculations in. Each time you download the back end file, your front end file is automatically updated.
      This idea of a front end file is also useful if you are not American and have dates in your download. Currently, all dates are downloaded in m/d/yy format, which is really unhelpful for the majority of people on the planet. If you use a front end spreadsheet you should be able to format your date column to d/m/yy or whatever is the format you want. You could do that with your downloaded spreadsheet, but would have to do that each time you do a download.
      There is another alternative and that is to build a query in your web app which has the field names as the first row, but this is hard work, requires data macros, lots of calculations in some queries and is limited in the number of columns you can do. I’ve done it in order to create a crosstab with 30 odd columns, but it is not for the faint-hearted.

  11. I’m impressed. I just love the idea that the Access Dev team can release new features and announce them without waiting for a major release is brilliant.

    So many new features get lost in the crowd when they all come out at once.

    I hope to see this blog announcing new features on a regular basis.

  12. Great Feature to Add, but poor implementation. Why would you just add it to existing apps? I am a developer and now my customers are concerned about app that allow their users to download information that they did not wish to have downloaded. I really do not understand why you would implement a feature this way? How Bizarre

    • Hi, AccessMan. If it helps to reassure your customers, you can tell them that the new feature does not allow their users to access any information that was not already available, it just makes it more convenient to get that information outside their Access Web App.
      The new feature does not bring down any data from the server that would not be brought down simply by paging through the datasheet.
      Since we didn’t want all existing apps to have to be updated specifically to enable the new feature, and the new feature does not allow users to download any information that they couldn’t already get, we decided it would provide the greatest benefit to enable it for all existing apps, and provide a way to hide the feature if a developer decide that was appropriate for their particular app.
      I hope that makes our design make a little more sense.

Comments are closed.