Access Report export to Excel

Question

Am trying to export Access Report to Excel. However, Excel displayed: "File Error: Data May Have been Lost" and format mismatched (see below attached). Any ideas how to fix this to ensure format exported from Access to Excel remains the same?

Answer 1

Hi BoltVoltes,

Thank you for posting in this community and I am sorry for this inconvenience caused to you.

According to this article: Export data to Excel (microsoft.com)

We noticed that it is a good idea to review the data that you want to export to make sure that it does not contain any error indicators or error values before performing an export procedure. If there are any errors, try to resolve them before you export the data to Excel. Otherwise, problems can occur during the export operation, and null values might be inserted into cells in the Excel worksheet. For more information about problems that can occur when exporting to Excel, see the section Troubleshoot missing and incorrect values.

And for rich text fields, the text is exported but the formatting is not. 

So, in this case, you may try to renew your Access data and sure that it does not contain any error indicators or error values. Then try to re-export the data to Excel.

If none of the columns appear to be formatted in the resulting worksheet, repeat the export operation, being sure to select the Export data with formatting and layout check box in the wizard. Conversely, if only some columns appear to be formatted differently from the versions in the source object, apply the formatting that you want manually in Excel.

Format a column in Excel

1. Open the destination Excel workbook, and then display the worksheet that contains the exported data.
2. Right-click a column or a selected range of cells, and then click Format Cells.
3. On the Number tab, under Categories, select a format, such as Text, Number, Date, or Time.
4. Click OK.

If the issue persists, may I know if the issue also happens to other access files?

Best Regards,

May

Answer 2

Dear May,

Thanks for your reply. How do I know there's error? From the Access Report everything seems to work fine and as you can see on my screenshot, Export data with formatting and layout is checked. It's just a hassle to have to adjust things in Excel every time Access Report is exported.

I have not experienced this in previous versions. However, this is a new database I am building using Access Microsoft Professional Plus 2021.

Answer 3

FYI. This is acontinuation of a previous thread.