Hi,



I have a query ( Using sql) containing data that i need to export
into excel.

I am currently exporting it by cut and paste the data, it is

not a very intuitive way of doing things, what i would prefer is if i

could have a button on a form which when it was clicked, it

automatically exported the query to a Excel file. Can anyone help me

with the VBA that would be neccessary, so i can connect excel and ms sql


--
Posted via http://dbforums.com

Set it up as a DTS job, using a server connection and an Excel connection.

Tie 'em together with a Transform Data Task.

Put your query into that.

Save.

Run.

The only problem you might run into is if your spreadsheet gets full.



"lh2001" <member38127 (AT) dbforums (DOT) com> wrote

If you want to use DTS in your application then have a look at this

Redistributing DTS with your program
(http://www.sqldts.com/default.aspx?225)

Remember Filenames etc are relative to the person pushing the button i.e.
use UNC paths.

The default behaviour of the Excel driver is to APPEND. To stop this you
cannot issue a DELETE against the spreadsheet in an ExecuteSQL task as the
driver does not support it but you can issue a DROP TABLE. You then
staright after issue a CREATE TABLE and then your datapump. Works a treat.


--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org