Excel Reports in VB.NET

[krallabandi]

Hi,

I am trying to generate Excel sheet using
Provider=Micros oft.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.Ol eDb.OleDbExcept ion'
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.xm l which is blank excel file with excel sheet
name iPOS. And also created a folder c:\temp\iPOSRep orts

Pls help to resolve this issue.

Imports System.Data.Ole Db
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\iPOSRe ports\" & des
fso.Copy("c:\te mp\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(sqlS tr)
Next
Return
End Sub

Private Sub InitializeConne ction()
excelConn = New OleDbConnection
excelConnStr = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & desPath & ";Extended
Properties=""Ex cel 8.0;HDR=YES"""
End Sub
Private Sub EstablishConnec tion()
excelConn.Conne ctionString = excelConnStr
excelConn.Open( )
End Sub
Private Sub executeSql(ByVa l sqlStr As String)
InitializeConne ction()
EstablishConnec tion()
excelComm = New OleDbCommand
excelComm.Comma ndType = CommandType.Tex t
excelComm.Comma ndText = sqlStr
excelComm.Conne ction = excelConn
excelComm.Execu teNonQuery()
MsgBox(sqlStr)
closeConnection ()
End Sub
Private Sub closeConnection ()
excelComm.Dispo se()
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.Applicati on
Dim WSheet As Excel.Worksheet

'Opens the Form27 Excel document
NewForm27.Workb ooks.Open("C:\P rogram
Files\97CS\Self Inspect\NewForm 27.xls")

'Puts stuff into the form
WSheet = NewForm27.Workb ooks.Item(1).Wo rksheets("Sheet 1")
WSheet.Cells(2, 5) = txtCheckList.Te xt
WSheet.Cells(3, 2) = txtQuestion.Tex t
WSheet.Cells(5, 2) = cmbAnswer.Text
WSheet.Cells(2, 12) = txtNumber.Text
WSheet.Cells(2, 2) = txtDateInspecte d.Text
WSheet.Cells(6, 2) = txtComments.Tex t
WSheet.Cells(9, 2) = txtRootCause.Te xt
WSheet.Cells(11 , 1) = txtRevDate1.Tex t

'Save and close excel sheet.
WSheet.SaveAs(" C:\Program Files\97CS\Self Inspect\Form27s \" &
txtCheckList.Te xt & ".xls")
NewForm27.Quit( )
"kr*********@gm ail.com" wrote:

Hi,

I am trying to generate Excel sheet using
Provider=Micros oft.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.Ol eDb.OleDbExcept ion'
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.xm l which is blank excel file with excel sheet
name iPOS. And also created a folder c:\temp\iPOSRep orts

Pls help to resolve this issue.

Imports System.Data.Ole Db
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\iPOSRe ports\" & des
fso.Copy("c:\te mp\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(sqlS tr)
Next
Return
End Sub

Private Sub InitializeConne ction()
excelConn = New OleDbConnection
excelConnStr = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & desPath & ";Extended
Properties=""Ex cel 8.0;HDR=YES"""
End Sub
Private Sub EstablishConnec tion()
excelConn.Conne ctionString = excelConnStr
excelConn.Open( )
End Sub
Private Sub executeSql(ByVa l sqlStr As String)
InitializeConne ction()
EstablishConnec tion()
excelComm = New OleDbCommand
excelComm.Comma ndType = CommandType.Tex t
excelComm.Comma ndText = sqlStr
excelComm.Conne ction = excelConn
excelComm.Execu teNonQuery()
MsgBox(sqlStr)
closeConnection ()
End Sub
Private Sub closeConnection ()
excelComm.Dispo se()
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.Applicati on
Dim WSheet As Excel.Worksheet

'Opens the Form27 Excel document
NewForm27.Workb ooks.Open("C:\P rogram
Files\97CS\Self Inspect\NewForm 27.xls")

'Puts stuff into the form
WSheet = NewForm27.Workb ooks.Item(1).Wo rksheets("Sheet 1")
WSheet.Cells(2, 5) = txtCheckList.Te xt
WSheet.Cells(3, 2) = txtQuestion.Tex t
WSheet.Cells(5, 2) = cmbAnswer.Text
WSheet.Cells(2, 12) = txtNumber.Text
WSheet.Cells(2, 2) = txtDateInspecte d.Text
WSheet.Cells(6, 2) = txtComments.Tex t
WSheet.Cells(9, 2) = txtRootCause.Te xt
WSheet.Cells(11 , 1) = txtRevDate1.Tex t

'Save and close excel sheet.
WSheet.SaveAs(" C:\Program Files\97CS\Self Inspect\Form27s \" &
txtCheckList.Te xt & ".xls")
NewForm27.Quit( )
"kr*********@gm ail.com" wrote:

Hi,

I am trying to generate Excel sheet using
Provider=Micros oft.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.Ol eDb.OleDbExcept ion'
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.xm l which is blank excel file with excel sheet name iPOS. And also created a folder c:\temp\iPOSRep orts

Pls help to resolve this issue.

Imports System.Data.Ole Db
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\iPOSRe ports\" & des
fso.Copy("c:\te mp\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(sqlS tr)
Next
Return
End Sub

Private Sub InitializeConne ction()
excelConn = New OleDbConnection
excelConnStr = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & desPath & ";Extended
Properties=""Ex cel 8.0;HDR=YES"""
End Sub
Private Sub EstablishConnec tion()
excelConn.Conne ctionString = excelConnStr
excelConn.Open( )
End Sub
Private Sub executeSql(ByVa l sqlStr As String)
InitializeConne ction()
EstablishConnec tion()
excelComm = New OleDbCommand
excelComm.Comma ndType = CommandType.Tex t
excelComm.Comma ndText = sqlStr
excelComm.Conne ction = excelConn
excelComm.Execu teNonQuery()
MsgBox(sqlStr)
closeConnection ()
End Sub
Private Sub closeConnection ()
excelComm.Dispo se()
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.Applicati on
Dim WSheet As Excel.Worksheet

'Opens the Form27 Excel document
NewForm27.Workb ooks.Open("C:\P rogram
Files\97CS\Self Inspect\NewForm 27.xls")

'Puts stuff into the form
WSheet = NewForm27.Workb ooks.Item(1).Wo rksheets("Sheet 1")
WSheet.Cells(2, 5) = txtCheckList.Te xt
WSheet.Cells(3, 2) = txtQuestion.Tex t
WSheet.Cells(5, 2) = cmbAnswer.Text
WSheet.Cells(2, 12) = txtNumber.Text
WSheet.Cells(2, 2) = txtDateInspecte d.Text
WSheet.Cells(6, 2) = txtComments.Tex t
WSheet.Cells(9, 2) = txtRootCause.Te xt
WSheet.Cells(11 , 1) = txtRevDate1.Tex t

'Save and close excel sheet.
WSheet.SaveAs(" C:\Program Files\97CS\Self Inspect\Form27s \" &
txtCheckList.Te xt & ".xls")
NewForm27.Quit( )
"kr*********@gm ail.com" wrote:

Hi,

I am trying to generate Excel sheet using
Provider=Micros oft.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.Ol eDb.OleDbExcept ion'
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.xm l which is blank excel file with excel sheet name iPOS. And also created a folder c:\temp\iPOSRep orts

Pls help to resolve this issue.

Imports System.Data.Ole Db
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\iPOSRe ports\" & des
fso.Copy("c:\te mp\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(sqlS tr)
Next
Return
End Sub

Private Sub InitializeConne ction()
excelConn = New OleDbConnection
excelConnStr = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & desPath & ";Extended
Properties=""Ex cel 8.0;HDR=YES"""
End Sub
Private Sub EstablishConnec tion()
excelConn.Conne ctionString = excelConnStr
excelConn.Open( )
End Sub
Private Sub executeSql(ByVa l sqlStr As String)
InitializeConne ction()
EstablishConnec tion()
excelComm = New OleDbCommand
excelComm.Comma ndType = CommandType.Tex t
excelComm.Comma ndText = sqlStr
excelComm.Conne ction = excelConn
excelComm.Execu teNonQuery()
MsgBox(sqlStr)
closeConnection ()
End Sub
Private Sub closeConnection ()
excelComm.Dispo se()
excelComm = Nothing
excelConn.Close ()
excelConn = Nothing
End Sub

End Module