SEARCH

How-To Geek

Export an Access 2003 Report Into Excel Spreadsheet

In the corporate environment, Excel is king. So when you have an Access database report that you would really prefer to analyze in Excel spreadsheet form, there’s an easy way to convert it.

To convert it, start by opening up your Access Report in Print Preview mode.

Make sure you have the Print Preview Toolbar showing. You just need to right click an empty area on your toolbar and select “Customize” and check the toolbar. 

From the Print Preview Taskbar click the drop down menu and select “Analyze It with Microsoft Office Excel”.

Your Report will now be in Excel for you to change and analyze the data.

Brian Burgess worked in IT for 10 years before pursuing his passion for writing. He's been a tech blogger and journalist for the past seven years, and can be found on his about me page or Google+

  • Published 05/16/07

Comments (44)

  1. Spurgeon Green

    Thank You! Thank You! You are the MAN!!

  2. Carolyn

    Hi, there. Have you been using Access 2007 at all? If so, do you also have a procedure for exporting a report from Access 2007 to Excel. On the Print Preview Taskbar, Excel is grayed out as an option. And I have not yet found a way to customize the Task Bars – other than the Quick Access toolbar.

    I’m about as frustrated with Access 2007 as a developer can be. Any help you can provide would be ever so vastly appreciated.

  3. Carolyn

    A follow up to my previous comment: Microsoft in its infinite wisdom no longer supports exporting reports to Excel. In its own words:

    “This behavior is by design. You cannot export reports to the Excel format in Access 2007. Microsoft has disabled this functionality.”

    I don’t know about other developers, but this downgrade puts me in a ton of trouble. I’m going to have to redesign, recreate, redo work already done for dozens of reports.

    Thanks for letting me vent!

  4. mysticgeek

    Carolyn,

    My opinion is that Access is a terrible application for report writing… and I have to write them a lot!

    However, our company is moving to Crystal Reports which is much easier and has a slick interface too.

    I however love a challenge … so I will see what I can come up with for a work around or some type of solution. I guess one way would be to export the report into Word and then import that into an Excel spreadsheet…

  5. Ahmed

    I too am in great need of this export to Excel feature. Did anybody found a solution to it on Access 2007?

  6. Bill Brastow

    We have an Acess database that we need to report out into Excel so you can do something with the information. We are seeking tools to accomplish that task.

  7. Rascal

    Using Access 2007 I have to create various reports of about 20 pages in length using spreadsheet like data in the middle (at present having to create 4 crosstabs going accross the page and lining them up). This is not easy or professional. Any help would be appreciated (what is crystal reports like – would it help with this type of report writing – many thanks

  8. Kannan

    Hi,

    While I’m exporting Access report to excel I’m getting overflow error at specific page. How can I debug these kind of errors?

    Any idea would be very much helpful…

    Regards
    Kannan

  9. mysticgeek

    Kannan,

    I too have run into this error. It sounds like the Access report is too large to fit into Excel. I would try to split it up into separate worksheets … Or you could just run the Query associated with the Report. Is it a “make table” query?

    This is probably a long shot … but have you tried a repair/compact of the DB?

  10. Jeff

    I feel it’s worth noting that you can’t export complex reports to Excel.
    If you have lines and boxes in the Access report, you’ll end up with a mess.
    Excel also doesn’t take the captions from the Access report, but the Label instead.
    Annoying.
    A workaround is to export it to Word (.rtf) and then copy and paste it to Excel.

  11. John

    The main reason for exporting Access reports to excel is it gives you a method of placing unrelated yet desired multiple queries of data as subreports in a single master report and thus transfer the whole set to Excel at one time. The key to this is the data is not related through a key field yet the boss wants it all on one excel sheet.

    Furthermore, Access reports is not the perfect reporting solution but it will get you 80% there so you only have 20% of adjustments to make. In effect, it is the data gatherer with some formating and positioning. It helps save time but does not create the perfect excel file or report.

    It appears that Microsoft so far has lost a court battle with someone from Guatamala that they decided to remove the excel export feature for reports in Access 2007, from MVP site. Similar to the activex content click to activate issue that is now corrected as of April 08 IE patches.

    So keep your Access 2003 and do not apply any patches to it or it may not export reports to excel either. As with the click to activate patch, it is possible that MS could bring back the export reports to excel sometime in the future, especially if enough of us write to MS and let them know that this is a feature users desire.

  12. Nico

    I have a problem which is related to this and was wondering if you had any suggestions.

    I have a table which lists all the managers for various departments. I have twenty-five other tables which list users, user IDs and access levels in each of the departments.

    I need to create a spreadsheet for each manager with tabs/workbooks for each of the twenty-five departments filtered only for their users.

    Is there a way to do this in Access?

    Thanks for any help you’re able to offer!

  13. Idun

    So you have 25 departments!? I would write all infos in one table and add one column containing department’s id. It makes more sens! You can then filter as you want in a query, and create reports much easier!

  14. Craig

    I also get the Overflow error when exporting data to Excel from an Access report. I know it is not due to any limitation in Excel since I can break report into two and then export each section to Excel just fine. I then take the two export files and combine them into one Excel worksheet just fine.

    I’d love to do this via a query, but the report consolidates/groups the data in a way that a query can’t

  15. Graeme

    I to am frustrated at not being able to export a report to excel like I had been doing in Access 2003. This is a real backward step. Has anybody found a way to get around it?

  16. mysticgeek

    From what I understand … Access 2007 dropped that feature… for who knows what reason. To be honest … since Access 2007 came out .. I have never used it for any of my IT work. I stick with 2003. There should be a way to get around it … I hope. I will try to find something.

  17. Susan

    I have a label list typed directly on labels can that be converted into access?

  18. David

    Just what I needed, though not for a report. Many thanks!

    I have both MS Office 2003 and 2007 installed. The only 2007 apps I regularly use are PowerPoint, OneNote, and Groove. Otherwise, it’s 2003.

  19. ibrahim naxim

    If you want to export access 2007 report to excel, follow these steps:-
    1- Open the report in access and export it as rtf format and save.
    2- Open the exported file and copy ALL
    3- Open Excel and select ALL of a blank sheet.
    4- Paste the data, you will see the message: “Data on the clip board is not the same size…. Do you want to paste the data any way?
    5- Click “OK”
    6- Select all the cells and double click between any of the columns.
    7- Adjust the column size as you wish.
    Thats all
    I HAVE NO OTHER SOLUTION

  20. Yesenia

    I have a report in Access 2003 and when I export via “Analyze It with Microsoft Office Excel” some of the data does not export correctly, for example I have a column that has numbers like 1123456 or 1123456-1 but when exported instead of showing 1123456-1 it shows a completely different number. I did not have this problem when exportting from Access 97. Any ideas?

  21. Kara

    I have a very large access report. It is sorted by manager. Is there any way to email the managers their appropriate section from this report?
    It has page breaks where needed and the managers name on the report.

  22. Phill

    If you want to export a report into excel, use the send object procedure to email the underlying query to yourself. You can select the format to send it in – excel being one for them

  23. Tim

    Ibrahim Naxim has the only solution that I have seen for Access 2007 to export to Excel. Unfortunately it is painful – much spreadsheet manipulation is needed.
    The closest way of eliminating the need to completely re-modify the Excel spreadsheet is to ensure the report you make in Access 2007 looks as much as a spreadsheet as possible.
    1. Ensure the all data fields have their own labels/titles across the top with the data directly under them (column headers) – put these titles/labels in the “Report Header” area of the report.
    2. Avoid grouping data in the Access report – keep all information in the detail area of the report – once the Report is copied into the spreadsheet the user can group as they wish
    3. Eliminate (from the Access report) page headers – just use the field labels across the top of the report as the “Report Header” ONLY. These will be your column titles in the spreadsheet.
    I do this for ONLY the reports that I need to export to a spreadsheet.
    Once the report generates follow Ibrahim’s instructions and you should have a decent report that requires just minimal manipulation when copied to a spreadsheet. It is still not perfect since empty fields mess things up a bit – but should save allot of time.

  24. TimB

    I too have problem after PC crash, with enforced move to 2007 suite. I have to generate order intake analysis reports daily, easy B4 with the “analyse in excel” option in excel XP and previous versions.
    Only solution I can think is to send the report as .xls, generating lots of raw data in a readable format for excel. Access tries to be helpful by letting you automate this using outlook task. Then use Excel to analyse the data using the various lookup cell options.

    It a lot of hard work at first to get all the formulas, cell values etc right, but afterwards with the automatic generation of the xls file through outlook, the actually daily report generating is now near enough instantaneous. The advantage is that even though you are generating a second, intermediate xls file, it is your well designed, formatted and presentable spreadsheet that is doing the hardwork for you.

  25. MrRagno

    Hello,

    You might be able to help me with this one. I’ve got a report on Access 2000, but in the report screen I lost the icons to export to Excel, is there any way of bringing them back? The database was created through VBA so I cant see any toolbars. Is there a keyboard shortcut for exporting to excel?

    thanks

  26. pcunkie7
  27. Welton

    I have a MS Access report that I am converting to Excel, but the comment column is not putting all the information into excel. (truncating)

  28. Quiggs

    EXPORTING AN ACCESS 2007 REPORT INTO EXCEL
    I found it is a lot less messy to, instead of exporting a report from Access to Excel, to import the report into Excel:
    1. Open Excel
    2. Select ‘Data’ tab
    3. Select ‘From Access’ on the ‘Get External Data’ toolbar
    4. Select your data source
    5. Select your Table/Report to import.
    Hope this helps. Give it a try.

  29. Bruce

    Thanks to all of our complaining Microsoft has issued sp2 which is supposed to add back support for exporting reports to Excel from Access 2007. Of course, Microsoft has changed their statement from ‘this is by design’ to ‘this has been identified as an issue’. Those folks are unreal: unfortunately they’re the only game in town.

  30. Amunafc

    It is impossible to directly export an Access Report to Excel, however you can just create a query based on the type of fields and data you are looking for and right-click the query and select export then excel. while in Excel, you can run a PIVOT TABLE to analyse your data.

  31. Roger

    I am trying to export an Access 2003 report (that also contains a subreport) into Excel and retain the formating. The closest I have come was to export it as a RTF file, open in Word, select all and cut and paste into Excel. Is there an easier way? or some way to write a macro so someone can press a magic button in Access to make this happen behind the scenes?

  32. MMJ

    I have a report in Access 2003 and trying to export to Excel 2003. The cells will only take 255 characters and truncate it. Any idea how to make the export take the full cell length? It’s nowhere in the thousands of characters, but a few are definitely over 255 characters. If I cut and paste that one cell from Access, it fits, all the text is there, but when i export, it cuts it off. Any ideas?

  33. Rahul

    Hi,
    I have exported MS Access Report to Excel but i am getting dot in the header between two words for a column. could you please suggest me why this is happening??? and whats need to be done to over come this problem.
    This dots were used in the reports but presently removed but still they are coming in the report.

  34. JYO

    i USE

  35. Evan

    When I export an access 2003 report to excel it always shows collapsible bars on the left hand side. How do I get rid of them without copying and pasting to a new sheet? Thanks

  36. Daolorie

    I’m not very familiar with Access, so I’m running into a problem. I’ve created a query in Access (2003), which has many, many more rows than Excel (2003)’s capability. Any suggestions on how I can share this report/ data sheet without analyzing in Excel?

    Any help would be greatly appreciated.

    Thanks

  37. TAHA

    Dear Sir/madam

    Please any one explane that i have fore woork sheet (sheet-1 job book the main data book) from this data want to reflect some data to sheet-2 & 3 & 4 (the sheet-2 invoice, sheet-3 covering letter, sheet-3 out shanding)
    please explain how it posible with hole explaction
    wating for u r reply

    Thk
    Taha

  38. tellio2

    To convert a access 2007 report into excel install office 2003 as well only ever using the convertion benifit. it works

  39. Raman Shori

    All formatting will gets disturbed whenever we try to export ACCESS Report to XL File. Only TEXT Field name appears on top row with second line as data.

    Is there any way to export report as-is to XL.

  40. Rolan

    Well, I am an experienced user, I have been able to export reports to excel, word, pdf, in Access 2007 in my computer, which has Office 2007 installed, the only problem I have is, that in the end user computer, who only has Access 2007 runtime installed, I can’t complete the export procedure, some file formats are missing…If someone can give a hint, I wound appreciate it.

  41. Ike

    I’m well experienced user of Access 2007. I built a database program for some group of cosmetic industry here in our country using Access 2007. But I encountered same problem as you are in exporting report Access 2007 to Excel 2007. The very best that I can share with you all is to 1.) open the report, convert it to .xml then open it with an excel. 2.) Convert it to PDF, then download PDF Converter sofware to Excel and convert it. 3.) Find the record source query of the report then export it to excel. 4.) If you know how to use the Visual Studio 2005 Crystal Report, you can link all of your tables and queries there and make a crystal report and export it to Excel. All Objects, line, txt, numbers are copied including grouping. What figures you have in report the same in excel, likes SAP/R3.

  42. arun

    ——————————————————————————–

    We have a Access database 2003.
    The feature to export report from Access database failed for few output file types. We are testing in windows 7/office 2007 system.
    there are no issues in exporting the report from Access database to the formats PDF, Snapshot, XML and XPS.
    The following message is displayed when we are trying to export the report from Access database to the formats Excel 97-Excel 2003 Workbook, HTML, Microsoft Excel 5.0/95 Workbook, Rich Text Format and Text files.

    “microsoft office access can’t complete the output operation”

    code:
    DoCmd.OutputTo acReport, stDocName

    All this works fine in windows xp/office 2007
    Please advice. Thanks

  43. Anand

    For all the 2007 users, sp2 allows you to import access database to excel

  44. Mary Grace Obo

    exporting to rtf then copying & pasting to excel worked for me..Thanks Jeff1

Enter Your Email Here to Get Access for Free:

Go check your email!