How to achieve "Export to Excel" functionality for a report in Access 2007

According to the new changes in Access 2007, You cannot export a report to an Excel format in Access 2007. This was a very handy feature in Access 2003 and most of the user will be very disappointed with this change.

The Microsoft Support article below provides various scenarios and possible workarounds for the problem.

https://support.microsoft.com/kb/934833

With this blog post, I am attempting to provide a possible solution for exporting your reports from Access 2007 in a way that can be used in Excel 2007.

Consider the following scenario:

You try to export a report to a Microsoft Excel format. To do this, you right-click the report in the navigation pane, and then you point to Export. In this scenario, you may find that the Excel option is unavailable.

Solution:
You can use export to XML option provided in Access 2007 for using the report data in Excel 2007. Given below the steps to do so:

 

1. Consider a Student database and you have one report which lists all the students as given in the image below:

report

2. In order to export this report -> Go to the Export tab in the Ribbon where you can see options as shown in the image below:

Options

3. Select XML File option from the list and you will get a Export wizard as shown in the image below:

Step 1: In the first step you need to select the file path and file name.

xmlwiz1

Step 2: In the second step you select the information that you want to export. There are three options here: 1. Data (XML) 2. Schema (XSD) 3. Style (XSL). You need to select the appropriate options here. If you want to use the XML file in Excel, I would recommend you to select both option 1 (data) and option 2 (schema).

wiz2

Step 3: Once you select the appropriate options in step 2, the files will be saved to the selected location.  and you will be redirected to step 3, in which there is a option to save the export steps so that next time when you choose to export to "XML File" - it will directly get the saved settings and perform the export operation.

wiz3

4. Now, you have the exported XML file and Schema( if selected). You want to use the XML file inside Excel 2007 - which is fairly easy. AS Excel 2007 has native XML support, you can open Excel 2007 UI and then select File->Open and select the XML file that you just exported in #3. Excel will prompt you for the open options. Select "As an XML Table".

 excelXML1 copy

5. If you don't have the XSD schema exported with XML file , you will get following message as a informational dialog.

excelXML2

6. Click ok and your data is ready to be used in Excel. See the image below:

excelXML3

I am sure many of us who used the Export to Excel feature in Access 2003 would love this blog post. Appreciate if you can post your comments or suggestions.