Export Format Error: Cannot Open File

Antwan McCoy
Antwan McCoy used Ask the Experts™
on
I have a Export button which I wrote code behind and receiving this error after the Export was successful.....Any suggestions?


Private Sub Command562_Click()
On Error GoTo SubError
       
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "BeneficiaryRMD", "C:\Distro\Distro Processing\Exports\Exp_Beneficiary.xlsx"
   
   
    MsgBox "File exported successfully", vbInformation + vbOKOnly, "Export Success"
 
SubExit:
    Exit Sub
   
SubError:
    MsgBox "Error Number:" & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "An error occured"
    GoTo SubExit
   
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
The .xlsx format is type = acSpreadsheetTypeExcel12Xml
PatHartman is correct.

If you leave out the extension from the filename, the (correct) extension will be added automatically.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "BeneficiaryRMD", "C:\Distro\Distro Processing\Exports\Exp_Beneficiary"

Open in new window


You can also see a list of the constants collection AcSpreadSheetType here:
https://docs.microsoft.com/en-us/office/vba/api/access.acspreadsheettype
Antwan McCoyDatabase Solutions Engineer

Author

Commented:
Thanks Pat and the Expert Exchange family...the acSpreadsheetTypeExcel12Xml type is for Xml, Excel 2010-2016 formats...acSpreadsheetTypeExcel12 is only up to 2007 formats...I looked it up...Thanks again and it's working properly now
Distinguished Expert 2017

Commented:
I believe the issue is that acSpreadsheetTypeExcel12 creates binary format files which have a different extension.

IMNSHO, When MS added the "12" names, they should have used acSpreadsheetTypeExcel12 for the "normal" .xlsx format and acSpreadsheetTypeExcel12Bin for the binary format.  Using XML for the "normal" format just confuses everyone whether it is more accurate or not.
Distinguished Expert 2017

Commented:
Hi Anders,
That help entry is incorrect in its description of acSpreadsheetTypeExcel12 and acSpreadsheetTypeExcel12XML.  If you use acSpreadsheetTypeExcel12 as the type and omit the extension, you get .xlsb which is the binary format.  The help entry says Excel12 is for O2010 but that format was introduced with O2007 and it says the XML type includes binary which it doesn't.

I sent a comment to Microsoft on the help page but who knows if anyone will ever act on it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial