Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-27
8
Medium Priority
?
1,185 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 1000 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:Meir Rivkin
Meir Rivkin earned 1000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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:Meir Rivkin
ID: 39281079
u welcome ;-)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

886 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