"krallaba***@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
>
>

> 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()
>
>
> "krallaba***@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
> >
> >

> 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()
>
>
> "krallaba***@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
> >
> >