Excel Reports in VB.NET

  • Thread starter Thread starter krallabandi
  • Start date Start date
K

krallabandi

Hi,

I am trying to generate Excel sheet using
Provider=Microsoft.Jet.OLEDB.4.0;

I am always getting the error while inserting data into any cell other
than A.

An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

I am greatful to you if anyone could correct this. Also appreciate if
you can give me info about this kind of excel report generation in
VB.NET.

My OS id windows XP pro, using Excel 2000.

Here is my code:
I am using c:\temp\show.xml which is blank excel file with excel sheet
name iPOS. And also created a folder c:\temp\iPOSReports

Pls help to resolve this issue.



Imports System.Data.OleDb
Imports System.IO

Module Module1

Private excelConn As OleDbConnection
Private excelComm As OleDbCommand
Private excelConnStr As String
Private fso As File
Private desPath As String

Sub Main()
Dim sqlStr As String
Dim des As String = "show" & Now.Month & Now.Day & Now.Year &
Now.Hour & Now.Minute & Now.Second & ".xls"
desPath = "c:\temp\iPOSReports\" & des
fso.Copy("c:\temp\show.xls", desPath)
fso = Nothing
Dim cnt As Integer
For cnt = 0 To 10
sqlStr = "Insert Into [iPOS$A" & cnt & ":A" & cnt & "]
Values ('Kiran here');"
executeSql(sqlStr)
Next
Return
End Sub

Private Sub InitializeConnection()
excelConn = New OleDbConnection
excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & desPath & ";Extended
Properties=""Excel 8.0;HDR=YES"""
End Sub
Private Sub EstablishConnection()
excelConn.ConnectionString = excelConnStr
excelConn.Open()
End Sub
Private Sub executeSql(ByVal sqlStr As String)
InitializeConnection()
EstablishConnection()
excelComm = New OleDbCommand
excelComm.CommandType = CommandType.Text
excelComm.CommandText = sqlStr
excelComm.Connection = excelConn
excelComm.ExecuteNonQuery()
MsgBox(sqlStr)
closeConnection()
End Sub
Private Sub closeConnection()
excelComm.Dispose()
excelComm = Nothing
excelConn.Close()
excelConn = Nothing
End Sub

End Module
 
Krallaband,

Please don't drop this message in every newsgroup you see and than even more
times than one.

In all the newsgroups I have seen are these questions.

The last year this problems are forever answered by Paul Clement. He was
still not active. So waiting for that looks me the best. When he still wants
to do that after that unbelivable multiposting from you.

Normally Paul answers them always (and he looks than in the newsgroups I
have seen you dropped this question in).

I don't think Paul, however a lot of people active in newsgroups don't like
that and get than the idea "that I don't answer".

Just to let you know.

Cor
 
Back
Top