VBA to export query to Excel

[randolphoralph]

I am trying to export data from Access to an Excel file without saving the file. I have been unable to get my macro to work. Right now when the macro runs it only opens a new Excel workbook but does copy the data into the workbook.

I believe the issue lies in the generated strSQL. I suspect that it has to do with the Status field value equal to IVR Call Needed. Maybe the spaces in the value?

Here is my code.

Private Sub Export()
'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    
    strSQL = "SELECT Letters.[Sponsor SSN], Letters.[Beneficiary name], Letters.[Phone Number] FROM Letters WHERE Letters.[Status]= ""IVR Call Needed"";"
    
    
'Step 2: Identify the database and query
    Set MyDatabase = CurrentDb
On Error Resume Next
    With MyDatabase
        .QueryDefs.Delete ("tmpOutQry")
        Set MyQueryDef = .CreateQueryDef("tmpOutQry", strSQL)
        .Close
    End With
'Step 3: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
'Step 4: Clear previous contents
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("Sheet1").Select
'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
    End With
End Sub

[ItsMe]

What is "IVR Call Needed" and why the double quotes?

Have you tried debug.print for your SQL string?

DEbug.Print strSQL

then view it in the immediate window.

[randolphoralph]

IVR Call Needed is the value found in the field Status field.

I added double quotes because when I added single quotes I get a Compile Error: Expected end of statement.

[randolphoralph]

The immediate window shows the following
SELECT Letters.[Sponsor SSN], Letters.[Beneficiary name], Letters.[Phone Number] FROM Letters WHERE Letters.[Status]= "IVR Call Needed";

[ItsMe]

If you are looking for the literal text "IVR Call Needed" try..

WHERE Letters.[Status]= 'IVR Call Needed';"

Your previous code was probably looking for quatations in the field "Status"

[randolphoralph]

I tried WHERE Letters.[Status]= 'IVR Call Needed';" and the Excel sheet is empty. I do see that a Query was created and stored with the data, but the data was not copied to the Excel sheet.

[randolphoralph]

It looks like Step 3...opening the query is not occuring.

[ItsMe]

I am not familiar with opening a recordset this way.
Set MyRecordset = MyQueryDef.OpenRecordset

Maybe
Set MyRecordset = MyDatabase.OpenRecordset("MyQueryDef", dbOpenSnapshot)

I am going to do a little research to try and understand the approach you have here. It could also be something with how you are writing to the cells....

[Monterey_Manzer]

I'm not sure why you need all the QueryDefs code. I manage to get by with just a DAO.Recordset. I would define the active workbook and worksheet and just copy from the recordset. Something like this:

Dim xl As Excel.Application, xlWb as Excel.Workbook, xlWs as Excel.Worksheet
Dim strSQL as String, rst as DAO.Recordset

Set xl = CreateObject("Excel.Application")
Set xlWb = xl.ActiveWorkbook
Set xlWs = xlActiveWorksheet

strSQL = "SELECT Letters.[Sponsor SSN], Letters.[Beneficiary name], Letters.[Phone Number] FROM Letters WHERE Letters.[Status]= 'IVR Call Needed';"

Set rst = CurrentDb.OpenRecordset(strSQL)

xlWs.Range("A2").CopyFromRecordset rst

Give that a shot and see what you get.

[ItsMe]

I was able to get this to work

'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyRecordset As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    
    strSQL = "SELECT Letters.[Sponsor SSN], Letters.[Beneficiary name], Letters.[Phone Number] FROM Letters WHERE Letters.[Status]= 'IVR Call Needed';"

'Step 2: Identify the database and query
    Set MyDatabase = CurrentDb
    Set MyRecordset = MyDatabase.OpenRecordset(strSQL, dbOpenSnapshot)
'Step 4: Clear previous contents
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("Sheet1").Select
'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
            xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
    End With

The way you were opening the recordset seemed to be the issue. No need to create a qryDef either

edit: I see I was a little late with a solution. But the same idea... Seems like you started with some code you copied from somewhere. Why don't you tell us what you are trying to acompllish. Maybe there is yet another way.

[randolphoralph]

ItsMe that solution worked perfectly. Thank you everyone fot their assistance with this.