Exporting from Access to Excel Run Time Error 31532 (1 Viewer)

[mccoydo]

Hi there, I'm trying to export queries from access to excel using the DoCmd option. The code (see below) works to a point - it exports some of the queries before I get a run time error:

"31532: Microsoft Access was unable to export the data".

The worksheet tabs also do not pick up the query name but instead return what looks like a temporary ID (e.g. ~TMPCLP118431). Have tried different file locations and versions of excel but the same thing keeps happening. Any ideas?

Thanks in advance for your help.

 Sub ExportAllQueries()

Dim qdf As QueryDef

Dim db As Database

    Set db = CurrentDb
    
    For Each qdf In db.QueryDefs
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qdf.Name, "C:\Users\mccoydo\Desktop\AllQueries.xls"
        Debug.Print qdf.Name
    Next
       
End Sub

 

[pbaldy]

There will be more than just your saved queries in that collection (rowsources, etc). I would test the name for starting with "qry" or whatever, to exclude the other items.

 

[mccoydo]

That sorted it pbaldy. I put in an if statement before the DoCmd "If Left(qdf.Name, 3) = "qry" Then..." and that worked.

Thanks!

 

[pbaldy]

Happy to help!