krallabandi
4/8/2005 1:50:00 PM
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