none
Excel can't open file created with acSpreadsheetTypeExcel9 RRS feed

  • Question

  • Using MS Office 2013.

    When I execute the command 

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!query_name, reports_directory & rs!file_name

    I get an .xlsx file, but when I try to open it in Excel 2013 I get the message

    "Excel cannot open the file 'Reg_List_By_Name.xlsx' because the file format or file extension is not valid..."

    If I change the transferspreadsheet type to acSpreadsheetTypeExcel8 it creates an .xls file that Excel will open, but the base font is always crappy.

    Any ideas why Office 2013 produces a file it can't open?

    Thanks.  -Fred

    Monday, October 9, 2017 3:33 PM

All replies

  • Hi Fred,

    The file extension doesn't necessarily dictates the type of file you get. Try using acSpreadsheetType12Xml to see if it make any difference.

    Hope it helps...

    Monday, October 9, 2017 4:16 PM
  • Hi Fred,

    A file with the XLSX file extension is a file created by Microsoft Excel version 2007 and later. And TransferSpreadsheet with acSpreadsheetTypeExcel9 would export a file in Microsoft Excel 2000 format.

    So if you want to export the file in Microsoft Excel 2000 format, you need update filename to xls extension, such as Reg_List_By_Name.xls.

    If you want to export file with xlsx extension, you need use acSpreadsheetTypeExcel12Xml to export the file.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 10, 2017 7:06 AM
  • I have encountered this error many times, mostly recently today. The error is a bit of misdirection. The solution in my situation was specifying the Range value, which becomes the destination worksheet name.

    The destination worksheet name defaults to the source table/query name. In my case the name was too long (>31 characters). Specifying a shorter worksheet name solved the problem.

    Perhaps the value in rs!query_name violates Excel sheet naming rules in some way.

    Perhaps try

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!query_name, reports_directory & rs!file_name,,"Reg_List_By_Name"

    Regards,

    James R. Mireles

    Houston, TX

    Friday, January 26, 2018 8:38 PM