You can use the following function to export material to a .xlsx file without having to deal with restrictions DoCmd.TransferSpreadsheet
Public Sub CustomExcelExport(QueryOrTableOrSQL As String, FileLocation As String)
Dim rs As DAO.Recordset
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
Set rs = CurrentDb.OpenRecordset(QueryOrTableOrSQL)
excelApp.Workbooks.Add
Dim colNo As Long: colNo = 1
Dim rowNo As Long: rowNo = 1
Dim fld As Variant
For Each fld In rs.Fields
excelApp.Cells(rowNo, colNo) = fld.Name
colNo = colNo + 1
Next fld
Do While Not rs.EOF
colNo = 1
rowNo = rowNo + 1
For Each fld In rs.Fields
excelApp.Cells(rowNo, colNo) = fld.Value
colNo = colNo + 1
Next fld
rs.MoveNext
Loop
excelApp.ActiveWorkbook.SaveAs FileLocation, 51 'xlOpenXMLWorkbook
excelApp.Quit
End Sub
Name it: CustomExcelExport "qryDataExport", strExportPath
source
share