I want to export from access to excel in xlsx, two tables in two different tabs and give each tab a name.
If I do something like this:
Private Sub Comando0_Click()
Dim miExcel As String
miExcel = "C:\Desktop\ExportarExcel.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Tabla1", miExcel, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Tabla2", miExcel, True
MsgBox "Exportación realizada correctamente", vbInformation, "OK"
End Sub
I can't open it in xlsx. It gives me an error. I have to save it with another extension to be able to open it.
If I do:
Private Sub Comando0_Click()
Dim miExcel As String
miExcel = "C:\Desktop\ExportarExcel.xlsx"
DoCmd.OutputTo acOutputTable, "Tabla1", "ExcelWorkbook(*.xlsx)", miExcel
DoCmd.OutputTo acOutputTable, "Tabla2", "ExcelWorkbook(*.xlsx)", miExcel
End Sub
I can open it in xlsx but only one table is saved, not both.
I don't know how it is to save it in two different tabs, and give each tab a specific name.
I have office professional plus 2016.
Your first option is the best, but you need to make some adjustments since you're using an Excel file type recommended for versions from several years ago:
Try this and comment. Greetings.