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 sConnectionStri ng As String
sConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & sSampleFolder & _
"Book7.xls;Exte nded Properties=Exce l 8.0;"
Dim objConn As New
System.Data.Ole Db.OleDbConnect ion(sConnection String)
objConn.Open()

'Add two records to the table.
Dim objCmd As New System.Data.Ole Db.OleDbCommand ()
objCmd.Connecti on = objConn
objCmd.CommandT ext = "Insert into [Sheet1$] (FirstName, LastName)" &
" values ('Bill', 'Brown')" <-- I try to amend
objCmd.ExecuteN onQuery()
objCmd.CommandT ext = "Insert into [Sheet1$] (FirstName, LastName)" &
" values ('Joe', 'Thomas')"
objCmd.ExecuteN onQuery()

'Close the connection.
objConn.Close()

[Paul Clement]

Re: Ado.net to excel ?

On Sun, 26 Feb 2006 23:45:15 +0800, "Agnes" <agnes@dynamict ech.com.hk> wrote:

¤ From
¤ http://support.microsoft.com/default...120121120120It
¤ 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 sConnectionStri ng As String
¤ sConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
¤ "Data Source=" & sSampleFolder & _
¤ "Book7.xls;Exte nded Properties=Exce l 8.0;"
¤ Dim objConn As New
¤ System.Data.Ole Db.OleDbConnect ion(sConnection String)
¤ objConn.Open()
¤
¤ 'Add two records to the table.
¤ Dim objCmd As New System.Data.Ole Db.OleDbCommand ()
¤ objCmd.Connecti on = objConn
¤ objCmd.CommandT ext = "Insert into [Sheet1$] (FirstName, LastName)" &
¤ " values ('Bill', 'Brown')" <-- I try to amend
¤ objCmd.ExecuteN onQuery()
¤ objCmd.CommandT ext = "Insert into [Sheet1$] (FirstName, LastName)" &
¤ " values ('Joe', 'Thomas')"
¤ objCmd.ExecuteN onQuery()
¤
¤ '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]

Re: Ado.net to excel ?

myInvoice is the table in SQL server,

"Paul Clement" <UseAdddressAtE ndofMessage@sws pectrum.com>
???????:ijc6021 vi5h5k48q4u5krk 60abm3r3kj0a@4a x.com...[color=blue]
> On Sun, 26 Feb 2006 23:45:15 +0800, "Agnes" <agnes@dynamict ech.com.hk>
> wrote:
>
> ¤ From
> ¤
> http://support.microsoft.com/default...120121120120It
> ¤ 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 sConnectionStri ng As String
> ¤ sConnectionStri ng = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> ¤ "Data Source=" & sSampleFolder & _
> ¤ "Book7.xls;Exte nded Properties=Exce l 8.0;"
> ¤ Dim objConn As New
> ¤ System.Data.Ole Db.OleDbConnect ion(sConnection String)
> ¤ objConn.Open()
> ¤
> ¤ 'Add two records to the table.
> ¤ Dim objCmd As New System.Data.Ole Db.OleDbCommand ()
> ¤ objCmd.Connecti on = objConn
> ¤ objCmd.CommandT ext = "Insert into [Sheet1$] (FirstName,
> LastName)" &
> ¤ " values ('Bill', 'Brown')" <-- I try to amend
> ¤ objCmd.ExecuteN onQuery()
> ¤ objCmd.CommandT ext = "Insert into [Sheet1$] (FirstName,
> LastName)" &
> ¤ " values ('Joe', 'Thomas')"
> ¤ objCmd.ExecuteN onQuery()
> ¤
> ¤ '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)[/color]

[Paul Clement]

Re: Ado.net to excel ?

On Tue, 28 Feb 2006 11:39:17 +0800, "Agnes" <agnes@dynamict ech.com.hk> wrote:

¤ 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={SQ L
Server};Server= (local);Databas e=DBName;Truste d_Connection=ye s].[myInvoice];"


Paul
~~~~
Microsoft MVP (Visual Basic)