From: mp on
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

"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
"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
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
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
>>
>>
>
>