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:

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:

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.

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).

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.

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".

 

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

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

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.

Comments

• Anonymous
  November 25, 2008
  But nothing like the old 2003! I'm talking about "groups in report".

• Anonymous
  February 22, 2009
  It worked just as said in the blog post. Thanks. But then why is the export to excel button shown but grayed-out? This means microsoft intended the feature to be there but didn't, in the end?

• Anonymous
  May 08, 2009
  The comment has been removed

• Anonymous
  June 03, 2009
  Of course Microsoft doesn't care about their customers!  They only care about money.  That's been true for years, ever since they started using the General public for their Beta testers.  That's why I always wait at least a year before purchasing a new Microsoft product - it takes them that long to fix the simple bugs. As far as removing the Export to Excel option, that was intentional.  Microsoft KNOWS that is a commonly used option.  The reason for removing it?  They're trying to drum up business for their Certified Microsoft Consultants network, who charge an arm and leg to show customers how to do the things that used to be available automatically.  Isn't Microsoft fun? There are alternatives to Access out there, and I'm starting to suggest to my clients to switch their databases.  Access is the top-selling database right now, but after the way they butchered Access 2007, that may not last!

• Anonymous
  April 13, 2010
  Thanks for this post.  It's a good workaround.  I can't believe that MS released Access without this option.  They did that once before when they removed the ability to save the Documenter report as a table but this omission affects many more of their customers.

• Anonymous
  December 17, 2010
  Some of the time due to settings the the Export to Excel button does not work but on my computer and 20 other computers in my office the Export to Excel button works and is not grayed out. The question is why it is grayed out some of the time.