Run time error 31532- Microsoft Access cannot export the data.

[amit_excelVBA]

Hi, I am using below code to export table from access 2016 to excel but getting error saying:

Run time error 31532: Microsoft Access cannot export the data


Public Function ExportToexcel_DD()

Dim output_fl As String


stName = "\PRO"
output_fl = CurrentProject.Path & stName & "_DROPS.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, TableName:="Dps_Arprts", FileName:=output_fl, Hasfieldnames:=True, Range:="Arpts"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Dps_Rnws", output_fl, True, "Rnws"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dps_Obs", output_fl, True, "Obs"

End Function

Please help
Thanks in Advance.

 

[stumac]

I cant reproduce the error, is it exporting a table or a query?

From google it looks like a lot of people who have had it put it down to Xml.

What happens if you change to:

[COLOR=#333333]Public Function ExportToexcel_DD()[/COLOR]

[COLOR=#333333]Dim output_fl As String[/COLOR]


[COLOR=#333333]stName = "\PRO"[/COLOR]
[COLOR=#333333]output_fl = CurrentProject.Path & stName & "_DROPS"[/COLOR]

[COLOR=#333333]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName:="Dps_Arprts", FileName:=output_fl, Hasfieldnames:=True, Range:="Arpts"[/COLOR]
[COLOR=#333333]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dps_Rnws", output_fl, True, "Rnws"[/COLOR]
[COLOR=#333333]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Dps_Obs", output_fl, True, "Obs"[/COLOR]

[COLOR=#333333]End Function[/COLOR]

 

[amit_excelVBA]

Hi Stumac, thanks for the response. I have solved it

 

[welshgasman]

Hi Stumac, thanks for the response. I have solved it

So please let others know how you solved it, in case they have the same issue.?

 

[amit_excelVBA]

Sure, the first thing whenever the versions of MS OFFICE tools is changed we need to check the reference of the vba library. I checked and activated below references from references under Tools menu in VBA section.

Microsoft Office 16.0 Access database engine object library

Microsoft office 16.0 Object Library

Microsoft Access 16.0 Object library.

 

[welshgasman]

Thank you.
No mention was made in the original post of an upgrade to Office. so that would not be a solution easily thought of.?

 

[amit_excelVBA]

Hmmm. My bad. But this surely will help for any queries in future