Exporting query to Excel spreadsheet

[KirRoyale]

Hi all, I’m pretty new to this site – and to Access and VBA! I wanted to import an Excel file to a table in my Access database, which I (eventually) accomplished with very simple code in a class module:
Private Sub ImportDataTest_Click()
DoCmd.TransferSpreadsheet acImport, , "test", "Job Search", True
DoCmd.Close
End Sub
Now, after writing a query on the results of the table, I want to export the query ‘testquery’ back into Excel (creating a new file called ‘Exporttest’). The Excel file should be in the same directory as the database. Again I have used very simple code, on the same class module:
Private Sub exportDataTest_Click()
DoCmd.TransferSpreadsheet acExport, "testquery", "Exporttest"
DoCmd.Close
End Sub
but I am getting a
Run-time error ‘13’:
Type mismatch
I have looked through this site and others and there are lots of complicated problems regarding file exports and I cannot find the answer to my problem. I would be grateful if someone could please advise.
Thanks in advance.

 

[JHB]

You are missing the spreadsheet type.

DoCmd.TransferSpreadsheet acExport, SpreadsheetTypeGoesInHere, "testquery", "Exporttest"

 

[KirRoyale]

JHB, thanks for that. I added "10" as the spreadsheet type, which removed the error message but, unfortunately the spreadsheet did not seem to be exported.
I found some code from one of the other threads and used that, which seems to have worked:

Private Sub exportDataTest_Click()
On Error GoTo Err_exportDataTest_Click
Dim stDocName As String
stDocName = "testquery"

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLSX, , 1, , , acExportQualityPrint

Exit_exportDataTest_Click:
Exit Sub
Err_exportDataTest_Click:
MsgBox Err.Description
Resume Exit_exportDataTest_Click

End Sub

----------------------------