CODEWITHSUNDEEP
excelms-accessvba
LuckyLuke82
LuckyLuke82

Reputation: 606

Access VBA - Export from query with query - to Excel

I have a query which shows joined records from 2 tables. Now I want to select certain records with desired ID from that query and export them to Excel. How can I do this ?

This doesn't work :

Dim SQL As String
 
SQL = "SELECT * FROM Created_Query" & _
         " WHERE ID=" & Me![Combobox]
         
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, SQL, FileName, True

I've also tried with DAO.Recordset like this:

Dim Desired As Recordset
Dim SQL As String
      SQL = "SELECT * FROM Created_Query" & _
         " WHERE ID=" & Me![Combobox]
Set Desired= CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

None of the above methods work. Where am I wrong ?

Upvotes: 0

Views: 6262

Answers (1)

LuckyLuke82
LuckyLuke82

Reputation: 606

Solved, CreateQueryDef is what I needed :)

Dim db As DAO.Database
Dim rs As Recordset
Set db = CurrentDb
Dim mySql As String
mySql = "SELECT * FROM Created_Query" & _
         " WHERE ID=" & Me![Combobox]


db.CreateQueryDef "temp", mySql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "temp", FileName, True
DoCmd.DeleteObject acQuery, "temp"

Upvotes: 3

Related Questions