Exporting data to excel not working.....

[Izzy]

Here is the code.

Dim conExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;"""
Dim cmdOLE As OleDb.OleDbCommand
Dim conOLE As OleDb.OleDbConnection
Dim strOLE As String
cmdOLE = New OleDb.OleDbCommand
conOLE = New OleDb.OleDbConnection(conExcel)
conOLE.Open()
With cmdOLE
.CommandText = "INSERT INTO [InvoiceAgingByCompany$] (F1,
F2) VALUES('HI','by')"
.CommandType = CommandType.Text
.Connection = conOLE
.ExecuteNonQuery()
End With
I'm trying to insert into a blank sheet and the sheet name is correct.
I read somewhere that if you specify "HDR=NO" in the connection string
it would automatically name your columns "F1" , "F2" and so on.
The error I get is "The INSERT INTO statement contains the following
unknown field name: 'F2'. Make sure you have typed the name correctly,

and try the operation again."
If I remove the "F2" field and value the statement completes just fine.

Any ideas would be greatly appreciated.

[Spam Catcher]

"Izzy" <is************@gmail.comwrote in news:1166564570.886639.286220
@a3g2000cwd.googlegroups.com:

Any ideas would be greatly appreciated.

I usually export to CSV - much more compatible and easier to do :-)

Take a look at FileHelpers project on SourceForge.

[scorpion53061]

see

http://www.kjmsolutions.com/datasetarray.htm
Izzy wrote:

Here is the code.

Dim conExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;"""
Dim cmdOLE As OleDb.OleDbCommand
Dim conOLE As OleDb.OleDbConnection
Dim strOLE As String
cmdOLE = New OleDb.OleDbCommand
conOLE = New OleDb.OleDbConnection(conExcel)
conOLE.Open()
With cmdOLE
.CommandText = "INSERT INTO [InvoiceAgingByCompany$] (F1,
F2) VALUES('HI','by')"
.CommandType = CommandType.Text
.Connection = conOLE
.ExecuteNonQuery()
End With
I'm trying to insert into a blank sheet and the sheet name is correct.
I read somewhere that if you specify "HDR=NO" in the connection string
it would automatically name your columns "F1" , "F2" and so on.
The error I get is "The INSERT INTO statement contains the following
unknown field name: 'F2'. Make sure you have typed the name correctly,

and try the operation again."
If I remove the "F2" field and value the statement completes just fine.

Any ideas would be greatly appreciated.

[Izzy]

Thank you Scorpion, that will work great.

Spam Catcher - I usually write to CSV also when a power user will be
manipulating the data. In this case however it needs to be
pre-formatted. Thanks for the response.

scorpion53061 wrote:

see

http://www.kjmsolutions.com/datasetarray.htm
Izzy wrote:

Here is the code.

Dim conExcel As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=NO;"""
Dim cmdOLE As OleDb.OleDbCommand
Dim conOLE As OleDb.OleDbConnection
Dim strOLE As String
cmdOLE = New OleDb.OleDbCommand
conOLE = New OleDb.OleDbConnection(conExcel)
conOLE.Open()
With cmdOLE
.CommandText = "INSERT INTO [InvoiceAgingByCompany$] (F1,
F2) VALUES('HI','by')"
.CommandType = CommandType.Text
.Connection = conOLE
.ExecuteNonQuery()
End With
I'm trying to insert into a blank sheet and the sheet name is correct.
I read somewhere that if you specify "HDR=NO" in the connection string
it would automatically name your columns "F1" , "F2" and so on.
The error I get is "The INSERT INTO statement contains the following
unknown field name: 'F2'. Make sure you have typed the name correctly,

and try the operation again."
If I remove the "F2" field and value the statement completes just fine.

Any ideas would be greatly appreciated.