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
Thanks!
CEKMAN
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
docmd.setwarnings false
DoCmd.RunSQL "SELECT test.test, test.id INTO mytable FROM test;"
docmd.transferspreadsheet.
DoCmd.DeleteObject acTable, "mytable"
docmd.setwarnings true
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
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)
There is a workaround however using a temporary query like:
Dim qd As QueryDef
Set qd = CurrentDb.CreateQueryDef("
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,d bopensnaps hot)
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
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,d
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
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.
ASKER
RichardCorrie,
Thanks for the code. Will this copy the field names also - or just the data?
CEKMAN
Thanks for the code. Will this copy the field names also - or just the data?
CEKMAN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
docmd.transferspreadsheet "SELECT * FROM " & me.table & ";", etc..
Nic;o)