Access VBA - Export from query with query - to Excel

Question

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 ?

In first option you try to export an SQL statement, not a query object. In the second you create a Record set, and don't export it. — marlan
– marlan, Commented Apr 10, 2016 at 20:27

LuckyLuke82 · Accepted Answer · 2016-04-10 20:29:09Z

3

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"

answered Apr 10, 2016 at 20:29

LuckyLuke82

6061 gold badge21 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access VBA - Export from query with query - to Excel

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related