asked on

Export Format Error: Cannot Open File

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

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anders Ebro (Microsoft MVP)

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

Mac M

ASKER

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

PatHartman

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.

PatHartman

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.