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.
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.
But nothing like the old 2003! I'm talking about "groups in report".
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?
1) Does not work from Print Preview - my clients do not get access to the Database Objects but access via a customised UI.
2) Does not work with unbound report
Like many others, I am totally confused, and deeply worried, about the fact that this feature was removed out of 2007. My clients use it in virtually 80% of all their reports.
I am worried because of the way the new Fluent UI Ribbon was introduced and the omission of features likes this was done. Don't Microsoft care about their clients. I make my living out of this stuff and I am pissed off.
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!
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.
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.