Results 1 to 8 of 8
  1. #1
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68

    Access 2010 Exporting Report to Excel, only 95 and 2003 file formats are avail

    I need to export the report into an excel spreadsheet so someone else (without access) to database can view it, sort it, play however they want to with it. The file formats avail to export to are 5.0-95 and 2003. There is no 2007-2010 format and when I export it it doesn't look the same as the report with columns out of place and some text boxes not even existing?



    How do I get around this?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    How are you exporting the data now. Have you tried Docmd.transferspreadsheet ?

  3. #3
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Quote Originally Posted by ItsMe View Post
    How are you exporting the data now. Have you tried Docmd.transferspreadsheet ?

    I am selecting "External Data" at the top of the screen and then selecting "Excel" from the "Export" group.

    I am not familiar with using the command you listed.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can export a table or a query to a blank spreadsheet using the following VBA.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "FullPathAndFileName", True

    THis example uses Excel 2000 format. You can change the argument to match your needs. Be sure to include the correct file extension type in the Full Path and Name of your Excel file that you are exporting data to.

    Here is a list of some constants that you can use
    http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

    acSpreadsheetTypeExcel7 or simply the number 5 would tell Access to save the file as 95 format.

  5. #5
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Quote Originally Posted by ItsMe View Post
    You can export a table or a query to a blank spreadsheet using the following VBA.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", "FullPathAndFileName", True

    THis example uses Excel 2000 format. You can change the argument to match your needs. Be sure to include the correct file extension type in the Full Path and Name of your Excel file that you are exporting data to.

    Here is a list of some constants that you can use
    http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

    acSpreadsheetTypeExcel7 or simply the number 5 would tell Access to save the file as 95 format.

    I don't want to export it on an old format. It is giving me no option but the 95 or 2000 format which is older than 2010 and therefore jumbles things up.
    I need to export it to a 2010 excel format which is not in the option of the link you provided.

    Is there another way?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If you have Access 2010 and Excel 2010, it should recognize acSpreadsheetTypeExcel12. Just be sure to use the correct extension .xlsx

  7. #7
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Quote Originally Posted by ItsMe View Post
    If you have Access 2010 and Excel 2010, it should recognize acSpreadsheetTypeExcel12. Just be sure to use the correct extension .xlsx

    So I used

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Sanitation ReOccuring Charges by Month Query", "\\SED-MAIN1\FolderRedirections\russellh\My Documents\Querries\Sanitation.xlsx", True

    Clicked "Database Tools" then "Visual Basic" then in the new screen "Insert Module" then Pasted the Code.

    Clicked "run" and it did. It created the file in the right directory.

    Yet I get the error that I can't open the file because it is in the wrong format.
    Any Ideas?

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I just now noticed you replied to this thread. I do not know why that happened. What file extension does the resulting file have?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting a Access Report to an Excel File
    By Coffee in forum Import/Export Data
    Replies: 2
    Last Post: 07-28-2014, 11:32 AM
  2. Import from Excel file fails because of date formats
    By wardw in forum Import/Export Data
    Replies: 1
    Last Post: 01-01-2014, 02:20 AM
  3. Need help exporting large Access file into Excel
    By phidelt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 06:14 PM
  4. Replies: 0
    Last Post: 09-26-2012, 12:20 PM
  5. Exporting 2010 Web Database report to Excel
    By rogstepper in forum Import/Export Data
    Replies: 2
    Last Post: 09-12-2012, 01:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums