Solved

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

Posted on 2013-06-27
8
1,089 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
return value based on substr 10 44
Array problem 6 47
VB.Net  Exporting Excel Woorkbook to PDF 7 58
VB.net VSTO Excel Ribbon error 4 9
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 article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

685 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