Export data to an Excel dynamic worksheet
Export your app data to an Excel dynamic worksheet to get the most up-to-date information from your app. When you export your data to an Excel dynamic worksheet and then make changes to your data in the app, you can use the refresh feature in Excel to get the latest information from the app.
In the example below, we downloaded an Excel dynamic worksheet then went back to the app and changed the Reserve for days column from 5 days to 10. Then opened the Excel worksheet and refreshed the data to get the latest information from the app.
A few things to note:
- You can export up to 100,000 rows at a time. You can’t export data to a dynamic worksheet in Excel for all tables. If you don’t see the option for a table, then it’s not available for that table.
- You can email a dynamic Excel file or store it as a shared file if the recipients are in the same domain as you. When recipients open the dynamic file, they only see the data that they have permission to view.
- Some system views can be exported only to a static Excel worksheet.
- Currency values are exported to Excel as numbers. To format the data as currency after you have completed the export, see Format numbers as currency.
- The date and time values that you see in the app show up only as Date when you export the file to Excel, but the cell actually shows both the date and time.
- If you're an app maker, you can use the Microsoft Power Apps Excel Add-in to download your app data and make edit in Excel and then save the data back to your app. For more information, see Open table data in Excel.
Export a dynamic worksheet
On the left nav, select a table.
On the command bar, select the Export to Excel menu and then select Dynamic Worksheet.
Select the columns to export and then select Export.
When the download is complete, navigate to the location of the downloaded file.
When you download a worksheet it should automatically save to your computer. However, if it doesn't then make sure that you save it before you open and edit it. Otherwise, you might get this error message: Excel cannot open or save any more documents because there is not enough available memory or disk space.
To fix the issue:
- Open Excel and go to File > Options > Trust Center Settings Center Settings > Protected View.
- In Protected View, clear all three items.
- Select OK > OK.
We still strongly recommend that you save and then open the data file rather than disabling protected view, which might put your computer at risk.
Open the saved Excel file.
If you see the security warning External Data Connections have been disabled, select Enable Content.
Go back to your app and update your app data.
To see your updates in the Excel dynamics worksheet, in Excel go to Data > Refresh All. If this doesn't work, see Refresh All doesn't work.
Refresh All doesn't work
After you use the Export to Excel command to export a file to your local computer and open the file by selecting Data > Refresh All. The data disappears and workbook appears blank.
This issue occurs when the data that you're accessing is password-protected and the Excel file can't submit passwords to external data sources. To resolve this issue, you must edit and save the web query.
In the Excel file, select the Data tab > Queries and Connections.
The Queries & Connections pane opens on the right of the window. On the Connections tab, right-click to select the query and then select Properties.
The Connection Properties window opens. On Definition tab, select Edit Query
If prompted, enter username and password. Enter the same user and password that you use to sign in to your app.
On the Edit Web Query window, select GO. An error message will show: Can't complete this action
Close the Edit Web Query window.
This should fix the issue. Refresh the data in the worksheet again by going to, Data > Refresh All.
Submit and view feedback for