Solved

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

Posted on 2013-06-27
8
1,051 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

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.

Question has a verified solution.

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

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now