Solved

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

Posted on 2013-06-27
8
1,067 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

776 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