Write to excel with odb

  • Thread starter Thread starter mp
  • Start date Start date
M

mp

net newb here...
i found this intro but dont' know where to go from here
Sub WriteExcelFileWithJet()

'If your purpose of use Excel is only to store data in a sheet, you do not
need to run Excel application at all. Simply use MS Jet OLDDB driver and
..NET System.Data.OleDb name space (OleDbConnection) to query/retrieve data
and update from/to Excel sheet. This way you will not need to deal the
famous Excel.Application remains in memory issue, which has been raised so
many times.

Dim ODB As System.Data.OleDb.OleDbConnection

ODB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\TEST.XLS"

ODB.Open()

ODB....how would I print "Hello" to cell A1 for instance on "Sheet1" or
rename sheet, etc...

End Sub

thanks

mark
 
mp said:
net newb here...
i found this intro but dont' know where to go from here
Sub WriteExcelFileWithJet()

'If your purpose of use Excel is only to store data in a sheet, you do not
need to run Excel application at all. Simply use MS Jet OLDDB driver and
.NET System.Data.OleDb name space (OleDbConnection) to query/retrieve data
and update from/to Excel sheet. This way you will not need to deal the
famous Excel.Application remains in memory issue, which has been raised so
many times.

Dim ODB As System.Data.OleDb.OleDbConnection

ODB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\TEST.XLS"

ODB.Open()

ODB....how would I print "Hello" to cell A1 for instance on "Sheet1" or
rename sheet, etc...

End Sub

thanks

mark
Click to expand...
found this sample but cant' get it to work...note exception text at end
started new windows project (vbnet 2008 express), put one button on form
created excel file "C:\testdb.xls" put "test me" in row 1 column 1

here's the code and error msg from clicking button
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Call WriteExcelFileWithJet()
End Sub

Sub WriteExcelFileWithJet()
Dim ds As New System.Data.DataSet()
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
cn = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data
source=C:\TestDB.XLS;Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from
[Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
'read row 1 column 1
MsgBox(ds.Tables(0).Rows(1).Item(1).ToString())

' System.IndexOutOfRangeException was unhandled
' Message = "There is no row at position 1."
End Sub
End Class

if i change the line to Row(0).Item(0) i dont' get an error, msgbox comes up
but is blank

not sure what i need to change.
thanks
mark
 
mp said:
mp said:
net newb here...
i found this intro but dont' know where to go from here
Sub WriteExcelFileWithJet()

'If your purpose of use Excel is only to store data in a sheet, you do
not need to run Excel application at all. Simply use MS Jet OLDDB driver
and .NET System.Data.OleDb name space (OleDbConnection) to query/retrieve
data and update from/to Excel sheet. This way you will not need to deal
the famous Excel.Application remains in memory issue, which has been
raised so many times.

Dim ODB As System.Data.OleDb.OleDbConnection

ODB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\TEST.XLS"

ODB.Open()

ODB....how would I print "Hello" to cell A1 for instance on "Sheet1" or
rename sheet, etc...

End Sub

thanks

mark
Click to expand...
found this sample but cant' get it to work...note exception text at end
started new windows project (vbnet 2008 express), put one button on form
created excel file "C:\testdb.xls" put "test me" in row 1 column 1

here's the code and error msg from clicking button
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Call WriteExcelFileWithJet()
End Sub

Sub WriteExcelFileWithJet()
Dim ds As New System.Data.DataSet()
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
cn = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data
source=C:\TestDB.XLS;Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from
[Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
'read row 1 column 1
MsgBox(ds.Tables(0).Rows(1).Item(1).ToString())

' System.IndexOutOfRangeException was unhandled
' Message = "There is no row at position 1."
End Sub
End Class

if i change the line to Row(0).Item(0) i dont' get an error, msgbox comes
up but is blank

not sure what i need to change.
thanks
mark
Click to expand...

continuing this conversation with myself...:-)
this weird fact emerges
Row(0) is row 2 in the excel file
..Item(0) is column 1 in the row
go figure
so if I put something in row 2 column 1 that is returned by the code above
with Row(0).Item(0).ToString()
now to figure out how to write to cells, not just read from
and also figure out how to read row 1...i'm thinking Row(-1) isn't going to
cut it....
thanks
mark
 
Hi,

I would look at reading the dataset like this personally, this will go
through each row. but remember that datasets / datatables are an arrary of
n-1, so row one is actually found at position 0 and row 2 is found at
position 1 etc.

for each row as datarow in ds.tables(0).rows
messagebox.show row(0).tostring
next

hope this helps

Simon

mp said:
mp said:
mp said:
net newb here...
i found this intro but dont' know where to go from here
Sub WriteExcelFileWithJet()

'If your purpose of use Excel is only to store data in a sheet, you do
not need to run Excel application at all. Simply use MS Jet OLDDB driver
and .NET System.Data.OleDb name space (OleDbConnection) to
query/retrieve data and update from/to Excel sheet. This way you will
not need to deal the famous Excel.Application remains in memory issue,
which has been raised so many times.

Dim ODB As System.Data.OleDb.OleDbConnection

ODB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\TEST.XLS"

ODB.Open()

ODB....how would I print "Hello" to cell A1 for instance on "Sheet1" or
rename sheet, etc...

End Sub

thanks

mark
Click to expand...
found this sample but cant' get it to work...note exception text at end
started new windows project (vbnet 2008 express), put one button on form
created excel file "C:\testdb.xls" put "test me" in row 1 column 1

here's the code and error msg from clicking button
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Call WriteExcelFileWithJet()
End Sub

Sub WriteExcelFileWithJet()
Dim ds As New System.Data.DataSet()
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
cn = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data
source=C:\TestDB.XLS;Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from
[Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
'read row 1 column 1
MsgBox(ds.Tables(0).Rows(1).Item(1).ToString())

' System.IndexOutOfRangeException was unhandled
' Message = "There is no row at position 1."
End Sub
End Class

if i change the line to Row(0).Item(0) i dont' get an error, msgbox comes
up but is blank

not sure what i need to change.
thanks
mark
Click to expand...

continuing this conversation with myself...:-)
this weird fact emerges
Row(0) is row 2 in the excel file
.Item(0) is column 1 in the row
go figure
so if I put something in row 2 column 1 that is returned by the code above
with Row(0).Item(0).ToString()
now to figure out how to write to cells, not just read from
and also figure out how to read row 1...i'm thinking Row(-1) isn't going
to cut it....
thanks
mark
Click to expand...
 
Thanks, I'll give it a try
I did manage to read row 2 by asking for Row(1), but not able to read row 1
by asking for Row(0)
I'll see what your loop will do
do you know how to write back to the excel file?
thanks
mark

Simon Whale said:
Hi,

I would look at reading the dataset like this personally, this will go
through each row. but remember that datasets / datatables are an arrary
of n-1, so row one is actually found at position 0 and row 2 is found at
position 1 etc.

for each row as datarow in ds.tables(0).rows
messagebox.show row(0).tostring
next

hope this helps

Simon

mp said:
mp said:
net newb here...
i found this intro but dont' know where to go from here
Sub WriteExcelFileWithJet()

'If your purpose of use Excel is only to store data in a sheet, you do
not need to run Excel application at all. Simply use MS Jet OLDDB
driver and .NET System.Data.OleDb name space (OleDbConnection) to
query/retrieve data and update from/to Excel sheet. This way you will
not need to deal the famous Excel.Application remains in memory issue,
which has been raised so many times.

Dim ODB As System.Data.OleDb.OleDbConnection

ODB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\TEST.XLS"

ODB.Open()

ODB....how would I print "Hello" to cell A1 for instance on "Sheet1" or
rename sheet, etc...

End Sub

thanks

mark

found this sample but cant' get it to work...note exception text at end
started new windows project (vbnet 2008 express), put one button on form
created excel file "C:\testdb.xls" put "test me" in row 1 column 1

here's the code and error msg from clicking button
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Call WriteExcelFileWithJet()
End Sub

Sub WriteExcelFileWithJet()
Dim ds As New System.Data.DataSet()
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
cn = New
System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" &
_
"data
source=C:\TestDB.XLS;Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from
[Sheet1$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
'read row 1 column 1
MsgBox(ds.Tables(0).Rows(1).Item(1).ToString())

' System.IndexOutOfRangeException was unhandled
' Message = "There is no row at position 1."
End Sub
End Class

if i change the line to Row(0).Item(0) i dont' get an error, msgbox
comes up but is blank

not sure what i need to change.
thanks
mark
Click to expand...

continuing this conversation with myself...:-)
this weird fact emerges
Row(0) is row 2 in the excel file
.Item(0) is column 1 in the row
go figure
so if I put something in row 2 column 1 that is returned by the code
above with Row(0).Item(0).ToString()
now to figure out how to write to cells, not just read from
and also figure out how to read row 1...i'm thinking Row(-1) isn't going
to cut it....
thanks
mark
Click to expand...
Click to expand...
 
Back
Top