"Paul Clement" wrote:

> On Tue, 15 Feb 2005 01:41:02 -0800, "Rhyd" <R***@discussions.microsoft.com> wrote:
>
> ¤ I need to link an excel file to a VB.Net program, so that the host computer
> ¤ doesn't need excel on it to read the cells. I don't need any functions of
> ¤ excel, just values from cells for viewing in textbox's.
> ¤
> ¤ I am trying to link the file using the database explorer, but, after right
> ¤ clicking and selecting new database ole connection, then Jet 4.0 and the file
> ¤ name, I am being told that :   "Could not find installable ISAM"
> ¤
> ¤ I have looked on the newsgroups, and I have made sure that I have the latest
> ¤ versions of the Jet 4.0 installed I have the latest service pack, Office 2003
> ¤ and VB 2005 express beta1. I'm thinking that maybe this a bit of a bad
> ¤ combination??
>
> You may want to post your connection string value. A syntax error in the connection string will also
> cause this error to occur.
>
>
> Paul ~~~ pclem***@ameritech.net
> Microsoft MVP (Visual Basic)
>

"Paul Clement" wrote:

> On Wed, 16 Feb 2005 04:47:03 -0800, "Rhyd" <R***@discussions.microsoft.com> wrote:
>
> ¤ Thank you Paul for your help
> ¤ I have solved the link to the excel file problem with the code below, but I
> ¤ now have a new problem!!
> ¤ How do I directly reference cells from the file I am now linking to, I know
> ¤ its something to do with the OledataReader that is why I have declared it,
> ¤ but I can't find out how to directly link data from specific cells in the
> ¤ file.
> ¤
> ¤ Here is my code:
> ¤
> ¤ Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> ¤ System.EventArgs) Handles Button1.Click
> ¤
> ¤             Dim conn As New OleDbConnection
> ¤             Dim Prices Reader As OleDbDataReader
> ¤             Dim cmd As New OleDbCommand
> ¤
> ¤             ' Set the connection string.
> ¤             Dim connString As String = "Data Source=" & _
> ¤                     "C:\ExDoorPrices2.xls;" & _
> ¤                     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> ¤                     "Extended Properties=Excel 8.0;"
> ¤
> ¤
> ¤             ' Open the connection.
> ¤             conn.ConnectionString = connString
> ¤             conn.Open()
> ¤
>
> You have to reference cells from either a DataSet/DataTable or DataReader. The cells to be queried
> can be generated through your SQL statement. Here are a few examples:
>
> 'Excel named range
> Select * from Range6x4
>
> 'Sheet name with a cell range
> Select * from [Sheet1$A1:C100]
>
> 'Sheet name only
> Select * from [Sheet2$]
>
> The following code example (starting after your code) uses a DataReader:
>
>         cmd  = conn.CreateCommand()
>
>         cmd .CommandText = "SELECT * FROM [Sheet1$]"
>         Prices  = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
>
>         While (Prices.Read())
>             Console.WriteLine(Prices.Item(0).ToString)
>             Console.WriteLine(Prices.Item(1).ToString)
>         End While
>
>
> Code example (starting after your code) uses a DataSet and DataTable:
>
>         Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet8$]", conn)
>
>         Dim ds As New DataSet("Workbooks")
>
>         da.Fill(ds, "Sheet8")
>
>         Dim dt As DataTable
>         dt = ds.Tables("Sheet8")
>
>         'binds DataTable to a DataGrid
>         frmMain.DataGrid1.SetDataBinding(ds, "Sheet8")
>
>         Dim RowIndex As Integer
>        
>         'enumerates the rows
>         For RowIndex = 0 To dt.Rows.Count - 1
>             Console.WriteLine(dt.Rows(RowIndex).Item(0))
>             'Console.WriteLine(dt.Rows(RowIndex).Item(1))
>             'Console.WriteLine(dt.Rows(RowIndex).Item(2))
>         Next
>
>         Dim drCurrent As DataRow
>
>        'another way to enumerate the rows
>         For Each drCurrent In dt.Rows
>             Console.WriteLine("{0} {1}", _
>                 drCurrent("F1").ToString, _
>                 drCurrent("F2").ToString)
>         Next
>
>        conn.Close()
>
>
> Paul ~~~ pclem***@ameritech.net
> Microsoft MVP (Visual Basic)
>

"Paul Clement" wrote:

> On Tue, 8 Mar 2005 01:49:07 -0800, "Rhyd" <R***@discussions.microsoft.com> wrote:
>
> ¤ 
> ¤ I understand this part of the code:
> ¤
> ¤ cmd  = conn.CreateCommand()
> ¤ 
> ¤         cmd .CommandText = "SELECT * FROM [Sheet1$]"
> ¤         Prices  = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
> ¤
> ¤ Its this next part I am having trouble with as I don't understand what this
> ¤ points to:
> ¤
> ¤        While (Prices.Read())
> ¤            Console.WriteLine(Prices.Item(0).ToString)
> ¤              Console.WriteLine(Prices.Item(1).ToString)
> ¤  End While
> ¤
> ¤ From the while loop I don't understand what the writeline command is
> ¤ writing, or where it is writing it. When this command is used in the program,
> ¤ there are no errors, except nothing happening.
> ¤
>
> The WriteLine code is simply displaying the value of each column (for each row in the DataReader) to
> the Console Window so you can see what the values are. It's just an example.
>
> ¤ Here is an example of what I want to do but in a different way:
> ¤
> ¤ Dim value as Single
> ¤ value = xlsheet.cells(2,2). Value
> ¤ Textbox1.Text = value
> ¤
> ¤ Before this code I opened an excel file, declaring the sheet the information
> ¤ was on as xlsheet.
> ¤ This is what I was doing before, but I identified the problem tha not all of
> ¤ the computers in the place of work have excel installed. As you can see a
> ¤ specific value is defined in a textbox after an action, I just need to apply
> ¤ this to the oledatareader or dataset
>
> If you need to retrieve the data from specific cells in the Sheet then you probably want to specify
> a range in your query. For example, the following will only query the cell 2,2 (a single row/column)
> of Sheet8 (or from B2 to B2):
>
>         Dim ConnectionString As String
>
>         Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
>         ExcelConnection.Open()
>
>         Dim ExcelCommand As System.Data.OleDb.OleDbCommand
>         ExcelCommand = ExcelConnection.CreateCommand()
>
>         ExcelCommand.CommandText = "SELECT * FROM [Sheet8$B2:B2]"
>         Dim ExcelReader As OleDbDataReader
>         ExcelReader = ExcelCommand.ExecuteReader(CommandBehavior.SequentialAccess)
>
>         Dim CellValue As String
>
>         If (ExcelReader.Read()) Then
>             CellValue = ExcelReader.Item(0).ToString
>         End If
>
>         ExcelConnection.Close()
>
>
> Paul ~~~ pclem***@ameritech.net
> Microsoft MVP (Visual Basic)
>