We help IT Professionals succeed at work.

Export to excel with SQL String

cekman
cekman asked
on
Medium Priority
365 Views
Last Modified: 2012-06-21
I know how to export a table or query to Excel - but how can I build a SQL string on the fly and then export that?

Thanks!
CEKMAN
Comment
Watch Question

Commented:
Just use in the code of the docmd.transferspreadsheet the SQL instead of a queryname like:
docmd.transferspreadsheet "SELECT * FROM " & me.table & ";", etc..

Nic;o)
its been a while since i have done it but if im correct the transferspredsheet function requires the name of a table or query and it wont accept an sql statement. in which case you can create a query using sql run that query through your transferspreadsheet function and then delete the query.
this code builds a table first (not a query) and then exports it, then deletes the table (just to keep things clean).

docmd.setwarnings false
DoCmd.RunSQL "SELECT test.test, test.id INTO mytable FROM test;"
docmd.transferspreadsheet.....
DoCmd.DeleteObject acTable, "mytable"
docmd.setwarnings true

Author

Commented:
nico5038 - have you tried your suggestion to see if it works?  I was pretty sure you had to use a query name or table name.

Bangerter,
Thanks for your suggestions. However - if I am forced to use a query/table. I will probably create a 'dummy' query called "QryExport" - then I will generate my SQL string and modify the query SQL. This way I'm not constantly copying a bunch of data.

CEKMAN

Commented:
Correct, was thinking again Access was more volatile as it is...

There is a workaround however using a temporary query like:

Dim qd As QueryDef

Set qd = CurrentDb.CreateQueryDef("tempqry")
qd.SQL = "Select * from city;"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, qd.Name, "c:\testxls.xls"
Set qd = Nothing

This will make the creation of a temp table obsolete and the processing will be twice as fast...

Nic;o)
Try:

1) add reference to Excel

2) create proc:

Public sub ExportToExcel(pstrSQL as string)

dim objExcel as Excel.application
dim exlBook as excel.workbook
dim exlSheet as excel.worksheet
dim exlRange as excel.Range

dim rec as recordset
dim db as database
dim fld as field
dim intCol as integer

set db = currentdb()
set rec = db.openrecordset(pstrsql,dbopensnapshot)

set objExcel = new excel.application
set exlbook = objexcel.workbooks.add
set exlsheet = exlbook.worksheets(1)
set exlrange = exlsheet.range("A1")
intCol = 1
for each fld in rec.fields
    exlrange.cells(1,intCol) = fld.name
    intCol = intCol +1
next
set exlrange = exlsheet.range("A2")
exlrange.copyfromrecordset rec

objexel.visible = true
objexcel.windowstate = vbmaximised
set exlrange = nothing
set exlsheet = nothing


Richard
however you want to do it. ultimately if you want to use the transferspreadsheet function you will have to either create the table/query.

Author

Commented:
RichardCorrie,
Thanks for the code. Will this copy the field names also - or just the data?

CEKMAN
Cekman,

The bit of the routine:
<
intCol = 1
for each fld in rec.fields
   exlrange.cells(1,intCol) = fld.name
   intCol = intCol +1
next
<

puts the field names in the first row of the spreadsheet.

The advantage of this routine is that you can put the output any whwere within a workbook!!

Richard