Solved

vb.net Get Cell values in excel to pass to a string; connect oledb

Posted on 2013-06-27
8
1,123 Views
Last Modified: 2013-06-28
Hello Experts,

I'm having trouble figuring out how to get cell values Y2,Y3,Y4 (these are text values) from excel using an oledb connection and putting them into separate strings.  I would rather connect this way than use any of Microsofts interop imports because I really need excel to stay closed.  Can anyone help?  Below is the only piece of code thanks to msdn and google I can figure out.  Also, Im not sure if I shoudl jet or ace in my connection string.

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
                    Dim MyConnection As System.Data.OleDb.OleDbConnection
                    MyConnection = New System.Data.OleDb.OleDbConnection( _
                    "provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "data source=" & filez.FullName & "; " & _
                    "Extended Properties=Excel 8.0")

                    ' Select the data from Sheet1 ([in-house$]) of the workbook.
                    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$Y2:Y5]", MyConnection)

Open in new window


Thanks in advance.
0
Comment
Question by:thomashospital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 250 total points
ID: 39280865
ACE vs JET depends on your version of Office, OS and 32bit vs 64bit.

You need to change this

 MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$Y2:Y5]", MyConnection)

to

 MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1]", MyConnection)

and then directly access values in column 25 of rows 2, 3, 4.
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 250 total points
ID: 39280868
add MyConnection.Close to close the excel instance.
btw, u can safely use excel interop to do the same job with no problem.
0
 

Author Comment

by:thomashospital
ID: 39280920
@codecruiser we are using a 32bit office on a 64 bit machine.

@sedgwick I will add the connection close.  I have tried to use the excel interop, and maybe i was coding wrong, but every time I did it opened excel.exe in the background.
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39280962
Interop does open Excel.

Do you have code after mycommand declaration to execute the command and get results?

You can add a datagrid to the form and use following code to test your connection

Dim dbadp As New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1]", 
                    "provider=Microsoft.Jet.OLEDB.4.0; " & _
                    "data source=" & filez.FullName & "; " & _
                    "Extended Properties=Excel 8.0")

Dim dTable As New DataTable
dbadp.Fill(dTable)
dbadp.Dispose

DataGridView1.DataSource = dTable

Open in new window

0
 

Author Comment

by:thomashospital
ID: 39280996
@CodeCruiser:  Actually this is what I am trying to figure out.  I should probably mention that this a console application not a form.  Im sitting here now trying to figure out how to pass the column 25 and rows 2,3,4 into a string.
0
 

Author Comment

by:thomashospital
ID: 39281031
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1]", MyConnection)

Open in new window

During debugging its telling me

The Microsoft Jet database engine could not find the object 'Sheet1'.  Make sure the object exists and that you spell its name and the path name correctly.
0
 

Author Comment

by:thomashospital
ID: 39281070
Its amazing how much difference a $ can make.

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

Open in new window


I was finally able to get the values into strings by doing the below:

Dim DS = New System.Data.DataSet
                    MyCommand.Fill(DS)
                    Dim Dt = DS.Tables(0).Rows(2)(24).ToString()

Open in new window



Thanks for the help guys
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39281079
u welcome ;-)
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question