Excel Reports in VB.NET

[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

[brix_zx2]

You are trying to put data into a sheet?? The way you got it looks like more
trouble than what you need. Or is it that you get data from this sheet as
well? Here is a way to just put it into the sheet.

'Sets up the Excel Interop
Dim NewForm27 As New Excel.Application
Dim WSheet As Excel.Worksheet

'Opens the Form27 Excel document
NewForm27.Workbooks.Open("C:\Program
Files\97CS\SelfInspect\NewForm27.xls")

'Puts stuff into the form
WSheet = NewForm27.Workbooks.Item(1).Worksheets("Sheet1")
WSheet.Cells(2, 5) = txtCheckList.Text
WSheet.Cells(3, 2) = txtQuestion.Text
WSheet.Cells(5, 2) = cmbAnswer.Text
WSheet.Cells(2, 12) = txtNumber.Text
WSheet.Cells(2, 2) = txtDateInspected.Text
WSheet.Cells(6, 2) = txtComments.Text
WSheet.Cells(9, 2) = txtRootCause.Text
WSheet.Cells(11, 1) = txtRevDate1.Text

'Save and close excel sheet.
WSheet.SaveAs("C:\Program Files\97CS\SelfInspect\Form27s\" &
txtCheckList.Text & ".xls")
NewForm27.Quit()
"kr*********@gmail.com" wrote:

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

[krallabandi]

But I don't want to go for Excel Interop.

Pls assist.

brix_zx2 wrote:

You are trying to put data into a sheet?? The way you got it looks like more trouble than what you need. Or is it that you get data from this sheet as well? Here is a way to just put it into the sheet.

'Sets up the Excel Interop
Dim NewForm27 As New Excel.Application
Dim WSheet As Excel.Worksheet

'Opens the Form27 Excel document
NewForm27.Workbooks.Open("C:\Program
Files\97CS\SelfInspect\NewForm27.xls")

'Puts stuff into the form
WSheet = NewForm27.Workbooks.Item(1).Worksheets("Sheet1")
WSheet.Cells(2, 5) = txtCheckList.Text
WSheet.Cells(3, 2) = txtQuestion.Text
WSheet.Cells(5, 2) = cmbAnswer.Text
WSheet.Cells(2, 12) = txtNumber.Text
WSheet.Cells(2, 2) = txtDateInspected.Text
WSheet.Cells(6, 2) = txtComments.Text
WSheet.Cells(9, 2) = txtRootCause.Text
WSheet.Cells(11, 1) = txtRevDate1.Text

'Save and close excel sheet.
WSheet.SaveAs("C:\Program Files\97CS\SelfInspect\Form27s\" &
txtCheckList.Text & ".xls")
NewForm27.Quit()
"kr*********@gmail.com" wrote:

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

[krallabandi]

But I don't want to go for Excel Interop.

Pls assist.

brix_zx2 wrote:

You are trying to put data into a sheet?? The way you got it looks like more trouble than what you need. Or is it that you get data from this sheet as well? Here is a way to just put it into the sheet.

'Sets up the Excel Interop
Dim NewForm27 As New Excel.Application
Dim WSheet As Excel.Worksheet

'Opens the Form27 Excel document
NewForm27.Workbooks.Open("C:\Program
Files\97CS\SelfInspect\NewForm27.xls")

'Puts stuff into the form
WSheet = NewForm27.Workbooks.Item(1).Worksheets("Sheet1")
WSheet.Cells(2, 5) = txtCheckList.Text
WSheet.Cells(3, 2) = txtQuestion.Text
WSheet.Cells(5, 2) = cmbAnswer.Text
WSheet.Cells(2, 12) = txtNumber.Text
WSheet.Cells(2, 2) = txtDateInspected.Text
WSheet.Cells(6, 2) = txtComments.Text
WSheet.Cells(9, 2) = txtRootCause.Text
WSheet.Cells(11, 1) = txtRevDate1.Text

'Save and close excel sheet.
WSheet.SaveAs("C:\Program Files\97CS\SelfInspect\Form27s\" &
txtCheckList.Text & ".xls")
NewForm27.Quit()
"kr*********@gmail.com" wrote:

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