Ado.net to excel ?

[Agnes]

From
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022#XSLTH4207121122120121120120It
seems we can export the data to an excel by a simple query. However, I try
to amend that statement into "insert int [Sheet1$] select * from myInvoice
", HOwerver, it really doesn't work . Does anyone got idea ?Thanks alot
'Establish a connection to the data source.
Dim sConnectionString As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sSampleFolder & _
"Book7.xls;Extended Properties=Excel 8.0;"
Dim objConn As New
System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()

'Add two records to the table.
Dim objCmd As New System.Data.OleDb.OleDbCommand()
objCmd.Connection = objConn
objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" &
" values ('Bill', 'Brown')" <-- I try to amend
objCmd.ExecuteNonQuery()
objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" &
" values ('Joe', 'Thomas')"
objCmd.ExecuteNonQuery()

'Close the connection.
objConn.Close()

 

[Paul Clement]

¤ From
¤ http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022#XSLTH4207121122120121120120It
¤ seems we can export the data to an excel by a simple query. However, I try
¤ to amend that statement into "insert int [Sheet1$] select * from myInvoice
¤ ", HOwerver, it really doesn't work . Does anyone got idea ?Thanks alot
¤ 'Establish a connection to the data source.
¤ Dim sConnectionString As String
¤ sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=" & sSampleFolder & _
¤ "Book7.xls;Extended Properties=Excel 8.0;"
¤ Dim objConn As New
¤ System.Data.OleDb.OleDbConnection(sConnectionString)
¤ objConn.Open()
¤
¤ 'Add two records to the table.
¤ Dim objCmd As New System.Data.OleDb.OleDbCommand()
¤ objCmd.Connection = objConn
¤ objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" &
¤ " values ('Bill', 'Brown')" <-- I try to amend
¤ objCmd.ExecuteNonQuery()
¤ objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" &
¤ " values ('Joe', 'Thomas')"
¤ objCmd.ExecuteNonQuery()
¤
¤ 'Close the connection.
¤ objConn.Close()
¤

What is myInvoice? Is this an Excel Worksheet in the current Workbook opened through your
connection?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Agnes]

myInvoice is the table in SQL server,

 

[Paul Clement]

¤ myInvoice is the table in SQL server,
¤

You need to hook up with SQL Server as well. See if the following helps:

"INSERT INTO [Sheet1$] SELECT * FROM [ODBC;Driver={SQL
Server};Server=(local);Database=DBName;Trusted_Connection=yes].[myInvoice];"


Paul
~~~~
Microsoft MVP (Visual Basic)