Write to excel with odb
in [VB.NET]
|
From: mp on 30 Jun 2010 18:58 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
From: mp on 30 Jun 2010 19:33 "mp" <nospam(a)thanks.com> wrote in message news:i0gi6o$sr9$1(a)news.eternal-september.org... > 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
From: mp on 30 Jun 2010 20:45 "mp" <nospam(a)thanks.com> wrote in message news:i0gk7g$4ls$1(a)news.eternal-september.org... > > "mp" <nospam(a)thanks.com> wrote in message > news:i0gi6o$sr9$1(a)news.eternal-september.org... >> 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 > 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
From: Simon Whale on 1 Jul 2010 06:05 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" <nospam(a)thanks.com> wrote in message news:i0gof4$k29$1(a)news.eternal-september.org... > "mp" <nospam(a)thanks.com> wrote in message > news:i0gk7g$4ls$1(a)news.eternal-september.org... >> >> "mp" <nospam(a)thanks.com> wrote in message >> news:i0gi6o$sr9$1(a)news.eternal-september.org... >>> 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 >> > > 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 > >
From: mp on 1 Jul 2010 09:02 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" <simon(a)nospam.com> wrote in message news:OO%23U8RQGLHA.5668(a)TK2MSFTNGP04.phx.gbl... > 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" <nospam(a)thanks.com> wrote in message > news:i0gof4$k29$1(a)news.eternal-september.org... >> "mp" <nospam(a)thanks.com> wrote in message >> news:i0gk7g$4ls$1(a)news.eternal-september.org... >>> >>> "mp" <nospam(a)thanks.com> wrote in message >>> news:i0gi6o$sr9$1(a)news.eternal-september.org... >>>> 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 >>> >> >> 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 >> >> > >
|
Pages: 1 Prev: SMTP: Bad Sequence of Commands Next: GridView set BackColor dinamically ERROR! |