Export to excel without using office automation

[Agnes]

I searched from previous post and got that there is a solution which export
the data to export without using Excel auotmation.

However, my database is SQL server, How can I amend objCmd.commandT est ??
I try the following insert statment but fail
"INSERT INTO [Sheet1$] SELECT * FROM
[ODBC;Driver={SQ LServer};Server =(210.22.14.201 );Database=DTS_ ACCOUNT_HK;Trus ted_Connection= yes].[arinvinfo];"


// Establish a connection to the data source.(copy from previous post)
System.Data.Ole Db.OleDbConnect ion objConn = new
System.Data.Ole Db.OleDbConnect ion(
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + m_strSampleFold er +
"Book7.xls;Exte nded Properties=Exce l 8.0;");
objConn.Open();

// Add two records to the table named 'MyTable'.
System.Data.Ole Db.OleDbCommand objCmd = new
System.Data.Ole Db.OleDbCommand ();
objCmd.Connecti on = objConn;
objCmd.CommandT ext = "Insert into MyTable (FirstName, LastName)" +
" values ('Bill', 'Brown')";
objCmd.ExecuteN onQuery();
objCmd.CommandT ext = "Insert into MyTable (FirstName, LastName)" +
" values ('Joe', 'Thomas')";
objCmd.ExecuteN onQuery();

// Close the connection.
objConn.Close() ;

[Paul Clement]

Re: Export to excel without using office automation

On Tue, 16 May 2006 22:03:59 +0800, "Agnes" <agnes@dynamict ech.com.hk> wrote:

¤ I searched from previous post and got that there is a solution which export
¤ the data to export without using Excel auotmation.
¤
¤ However, my database is SQL server, How can I amend objCmd.commandT est ??
¤ I try the following insert statment but fail
¤ "INSERT INTO [Sheet1$] SELECT * FROM
¤ [ODBC;Driver={SQ LServer};Server =(210.22.14.201 );Database=DTS_ ACCOUNT_HK;Trus ted_Connection= yes].[arinvinfo];"
¤

What is the error?


Paul
~~~~
Microsoft MVP (Visual Basic)

[Agnes]

Re: Export to excel without using office automation

There is an error ODBC {SQLServer}(IP. ....) connection fail


"Paul Clement" <UseAdddressAtE ndofMessage@sws pectrum.com>
???????:5bnj62l 9ae805pq0t16f1q 96bj198av4hn@4a x.com...[color=blue]
> On Tue, 16 May 2006 22:03:59 +0800, "Agnes" <agnes@dynamict ech.com.hk>
> wrote:
>
> ¤ I searched from previous post and got that there is a solution which
> export
> ¤ the data to export without using Excel auotmation.
> ¤
> ¤ However, my database is SQL server, How can I amend objCmd.commandT est
> ??
> ¤ I try the following insert statment but fail
> ¤ "INSERT INTO [Sheet1$] SELECT * FROM
> ¤
> [ODBC;Driver={SQ LServer};Server =(210.22.14.201 );Database=DTS_ ACCOUNT_HK;Trus ted_Connection= yes].[arinvinfo];"
> ¤
>
> What is the error?
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)[/color]

[Ken Tucker [MVP]]

RE: Export to excel without using office automation

Agnes,

Here is a link on how to create a spreadsheet with an xml
transform. The sample is for a web application but will work with a windows
forms application.

http://support.microsoft.com/default.aspx?scid=kb;en-us;319180

Ken
--------------------

"Agnes" wrote:
[color=blue]
> I searched from previous post and got that there is a solution which export
> the data to export without using Excel auotmation.
>
> However, my database is SQL server, How can I amend objCmd.commandT est ??
> I try the following insert statment but fail
> "INSERT INTO [Sheet1$] SELECT * FROM
> [ODBC;Driver={SQ LServer};Server =(210.22.14.201 );Database=DTS_ ACCOUNT_HK;Trus ted_Connection= yes].[arinvinfo];"
>
>
> // Establish a connection to the data source.(copy from previous post)
> System.Data.Ole Db.OleDbConnect ion objConn = new
> System.Data.Ole Db.OleDbConnect ion(
> "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + m_strSampleFold er +
> "Book7.xls;Exte nded Properties=Exce l 8.0;");
> objConn.Open();
>
> // Add two records to the table named 'MyTable'.
> System.Data.Ole Db.OleDbCommand objCmd = new
> System.Data.Ole Db.OleDbCommand ();
> objCmd.Connecti on = objConn;
> objCmd.CommandT ext = "Insert into MyTable (FirstName, LastName)" +
> " values ('Bill', 'Brown')";
> objCmd.ExecuteN onQuery();
> objCmd.CommandT ext = "Insert into MyTable (FirstName, LastName)" +
> " values ('Joe', 'Thomas')";
> objCmd.ExecuteN onQuery();
>
> // Close the connection.
> objConn.Close() ;
>
>
>[/color]

[Paul Clement]

Re: Export to excel without using office automation

On Tue, 16 May 2006 22:51:52 +0800, "Agnes" <agnes@dynamict ech.com.hk> wrote:

¤ There is an error ODBC {SQLServer}(IP. ....) connection fail
¤

That would seem to indicate a problem with the connection string or SQL Server configuration.

You might want to check out the connection string requirements for connecting via an IP address:

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer

Paul
~~~~
Microsoft MVP (Visual Basic)