Link to home
Start Free TrialLog in
Avatar of cekman
cekman

asked on

Export to excel with SQL String

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
Avatar of nico5038
nico5038
Flag of Netherlands image

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)
Avatar of Bangerter
Bangerter

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
Avatar of cekman

ASKER

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
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.
Avatar of cekman

ASKER

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

CEKMAN
ASKER CERTIFIED SOLUTION
Avatar of RichardCorrie
RichardCorrie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial