Export SQL Statement to Excel

[Steve Jensen]

I know I have seen this somewhere, but cannot find it now. How can one use
VBA to export a SQL select statement to Excel (not a saved query or a table,
but a statement like "Select * from Table1 WHERE Field1=2 Order By Field0")?

TransferSpreadsheet would be really useful but it only takes table or query
names not the underlying SQL statement.

 

[Stefan Hoffmann]

hi Steve,

I know I have seen this somewhere, but cannot find it now. How can one use
VBA to export a SQL select statement to Excel (not a saved query or a table,
but a statement like "Select * from Table1 WHERE Field1=2 Order By Field0")?

Try this:

On Local Error Resume Next

Dim ea As Object ' Excel.Application
Dim es As Object ' Excel.Worksheet

Dim SQL As String

Set ea = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set ea = CreateObject("Excel.Application")
End If
ea.Visible = True
ea.Workbooks.Add
Set es = ea.ActiveSheet

SQL = CurrentDb.QueryDefs.Item("yourQuery").SQL
es.Range("A1").Value = SQL

Set es = Nothing
Set ea = Nothing


mfG
--> stefan <--

 

[Steve Jensen]

Thanks. I tried your code, but it just opened an Excel workbook with the SQL
statement in cell A1. It did not return the results of the query.

 

[Steve Jensen]

BTW, I just want to create the file, not open Excel with the data.

 

[Stefan Hoffmann]

hi Steve,

Thanks. I tried your code, but it just opened an Excel workbook with the SQL
statement in cell A1. It did not return the results of the query.

Sorry, but then your subject is really misleading. My code exports the
statement, not the data...

Use the CurrentDb.CreateQueryDef to create a new query and use
DoCmd.TransferSpreadsheet with it.


mfG
--> stefan <--

 

[Steve Jensen]

OK, that is what I have been doing. I was just hoping there was a built-in
method.

Here is my code:

dim db as DAO.Database
dim i as Integer
dim td as object

'drop the query if it exists from the last time this function was used

For i = 0 To db.QueryDefs.count - 1
Set td = db.QueryDefs(i)
If td.Name = "xrQuery" Then
db.Execute "DROP TABLE [" & td.Name & "];"
End If
Next i

'create a new query with the SQL string passed to the procedure

db.CreateQueryDef "xrQuery", strSQL

'export the query to Excel

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "xrQuery",
"C:\Data\xrQuery.xls", True

 

[Stefan Hoffmann]

OK, that is what I have been doing. I was just hoping there was a built-in
method.

No, try this:

---
Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb

End Property

Public Sub ExportToExcel(ASQL As String, AFileName As String)

On Local Error Resume Next

Const QUERY_NAME As String = "tempQueryName"

Dim qd As DAO.QueryDef

Set qd = CurrentDbC.CreateQueryDef(QUERY_NAME, ASQL)
Set qd = CurrentDbC.QueryDefs.Item(QUERY_NAME)
qd.SQL = ASQL
Set qd = Nothing

On Local Error GoTo LocalError

DoCmd.TransferSpreadsheet acExport, , QUERY_NAME, AFileName, True

Exit Sub

LocalError:
MsgBox Err.Number & " " & Err.Description

End Sub