Simple: Excel Jet 4.0 Link vb 2005

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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??
 
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
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()

Thanks again
 
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul Clement said:
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...

Hi Paul thanks again for your reply
From what you've said,I still can't achieve what I need. Basically when the
user clicks a button for example, I want a specific value to be displayed in
a textbox, say column B, Row 5, as a decimal.
Thanks alot for your help again
Rhyd
 
¤ > 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 ~~~ (e-mail address removed)
¤ > Microsoft MVP (Visual Basic)
¤ >
¤
¤ Hi Paul thanks again for your reply
¤ From what you've said,I still can't achieve what I need. Basically when the
¤ user clicks a button for example, I want a specific value to be displayed in
¤ a textbox, say column B, Row 5, as a decimal.
¤ Thanks alot for your help again
¤ Rhyd


I need a little more info. Where are you stuck? Are you having a problem with the actual query of
the Excel data?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
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.

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

Thanks again Paul

Rhyd
 
¤
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Paul Clement said:
¤
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
Click to expand...

Thanks again Paul,
but after using the code you have advised, with a little modification to
suit what I am doing, the textbox just returns a zero value, which isn't the
value in the cell. My co-ordinates and the sheet is correct, and I feel that
this zero is just a null value, I don't know why though.

Do you know how I can create a connection through the designer view with the
database explorer, because i have also tried this but I am informed when I
test the connection that it only reads .mdb's and .mdf's.

Would an odbc connection be a better option, considering what I want to
achieve and the circumstances??

Thanks again
Rhyd
 
¤ Thanks again Paul,
¤ but after using the code you have advised, with a little modification to
¤ suit what I am doing, the textbox just returns a zero value, which isn't the
¤ value in the cell. My co-ordinates and the sheet is correct, and I feel that
¤ this zero is just a null value, I don't know why though.
¤

Try adding the IMEX parameter to your connection string to see if it makes any difference:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;" & _
"Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

¤ Do you know how I can create a connection through the designer view with the
¤ database explorer, because i have also tried this but I am informed when I
¤ test the connection that it only reads .mdb's and .mdf's.
¤

There seems to be a quirk in the wizard that causes this to happen. If you try it a second time
(without closing the Data Link Properties dialog) it should create a new Data Connection.

1) Select Microsoft Jet 4.0 OLEDB Provider
2) Select Excel File and blank out User Name
3) Set Extended Properties on All tab to Excel 8.0
4) Click OK

Repeat above four steps and Data Connection will be added.

¤ Would an odbc connection be a better option, considering what I want to
¤ achieve and the circumstances??

ODBC technology is a bit older than OLEDB and I don't think it will really help in this instance.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks again paul
You've been really helpful

BUT!!...

I keep getting a zero in the textbox!
Everything is correct in my code, as I wouldn't even get a zero if it
wasn't, but I don't understand why it doesn't work!
I was told by my cousin that under the extended properties in the code that
The
Excel 8.0

Should read

Excel 2000 or Excel 2003

But when I try this I just get the same error as before "Couldn't Find
installable ISAM"
I know this is because the path is wrong, but also Excel 11.0 returns the
same error even though through my references I have selected Microsoft Excel
11.0 Object Library.

The Data Connection method you described through the Designer worked
Perfectly, and the connection was confirmed. After this though i don't know
how to refer to this connection through my code or otherwise. I feel that at
least through this way the connection is confirmed, and it may be the best
way to progress, if my coding problems can't be resolved.
 
¤ Thanks again paul
¤ You've been really helpful
¤
¤ BUT!!...
¤
¤ I keep getting a zero in the textbox!
¤ Everything is correct in my code, as I wouldn't even get a zero if it
¤ wasn't, but I don't understand why it doesn't work!

You're getting a zero value or null? Can you describe an example of your Workbook? Such as how many
rows or columns it has, whether the column values are based upon worksheet formulas, what some of
the values are?

¤ I was told by my cousin that under the extended properties in the code that
¤ The
¤ Excel 8.0
¤
¤ Should read
¤
¤ Excel 2000 or Excel 2003
¤
¤ But when I try this I just get the same error as before "Couldn't Find
¤ installable ISAM"
¤ I know this is because the path is wrong, but also Excel 11.0 returns the
¤ same error even though through my references I have selected Microsoft Excel
¤ 11.0 Object Library.
¤

Excel 8.0 is the appropriate Extended Property value. There is no higher version, but the ISAM will
support newer versions of Excel.

¤ The Data Connection method you described through the Designer worked
¤ Perfectly, and the connection was confirmed. After this though i don't know
¤ how to refer to this connection through my code or otherwise. I feel that at
¤ least through this way the connection is confirmed, and it may be the best
¤ way to progress, if my coding problems can't be resolved.

You can drag the Data Connection from Server Explorer to a WinForm in design mode. That will make
the object available to the code behind your WinForm (just like the control instances you've dragged
to your WinForm from the Toolbox).


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top