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.commandTest ??
I try the following insert statment but fail
"INSERT INTO [Sheet1$] SELECT * FROM
[ODBC;Driver={SQLServer};Server=(210.22.14.201);Dat abase=DTS_ACCOUNT_HK;Trusted_Connection=yes].[arinvinfo];"
// Establish a connection to the data source.(copy from previous post)
System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
"Book7.xls;Extended Properties=Excel 8.0;");
objConn.Open();

// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new
System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();

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

[Paul Clement]

On Tue, 16 May 2006 22:03:59 +0800, "Agnes" <ag***@dynamictech.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.commandTest ??
¤ I try the following insert statment but fail
¤ "INSERT INTO [Sheet1$] SELECT * FROM
¤ [ODBC;Driver={SQLServer};Server=(210.22.14.201);Dat abase=DTS_ACCOUNT_HK;Trusted_Connection=yes].[arinvinfo];"
¤

What is the error?
Paul
~~~~
Microsoft MVP (Visual Basic)

[Agnes]

There is an error ODBC {SQLServer}(IP.....) connection fail
"Paul Clement" <Us***********************@swspectrum.com>
???????:5b********************************@4ax.com ...

On Tue, 16 May 2006 22:03:59 +0800, "Agnes" <ag***@dynamictech.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.commandTest
??
¤ I try the following insert statment but fail
¤ "INSERT INTO [Sheet1$] SELECT * FROM
¤
[ODBC;Driver={SQLServer};Server=(210.22.14.201);Dat abase=DTS_ACCOUNT_HK;Trusted_Connection=yes].[arinvinfo];"
¤

What is the error?
Paul
~~~~
Microsoft MVP (Visual Basic)

[Ken Tucker [MVP]]

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...b;en-us;319180

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

"Agnes" 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.commandTest ??
I try the following insert statment but fail
"INSERT INTO [Sheet1$] SELECT * FROM
[ODBC;Driver={SQLServer};Server=(210.22.14.201);Dat abase=DTS_ACCOUNT_HK;Trusted_Connection=yes].[arinvinfo];"
// Establish a connection to the data source.(copy from previous post)
System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strSampleFolder +
"Book7.xls;Extended Properties=Excel 8.0;");
objConn.Open();

// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new
System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" +
" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();

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

[Paul Clement]

On Tue, 16 May 2006 22:51:52 +0800, "Agnes" <ag***@dynamictech.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....erForSQLServer
Paul
~~~~
Microsoft MVP (Visual Basic)