>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);Database=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();


Get your data from SQL server into a ds. then this is the jist of the final idea on a class for export to a file that can be read by Excel

' Read the dataset in a XmlDataDocument
Dim loXmlDataDoc As XmlDataDocument = New XmlDataDocument(toDataSet)

Dim loXslCompiledTransform As Xsl.XslCompiledTransform = New Xsl.XslCompiledTransform

' Get the Excel stylesheet into memory
Dim loMemorySteam As New FileStream(tcTransformationStyleSheet, FileMode.Open, FileAccess.Read)

Dim loXmlTextReader As XmlTextReader = New XmlTextReader(loMemorySteam)
loXslCompiledTransform.Load(loXmlTextReader, Nothing, Nothing)
Dim loStringWriter As New StringWriter()
loXslCompiledTransform.Transform(loXmlDataDoc, Nothing, loStringWriter)

' Try to save the file
If Not oApp.CreateFile(loStringWriter.ToString(), tcFile) Then
Return False
End If

Return True
End Function

End Class