asked on

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

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.

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Meir Rivkin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thomashospital

ASKER

@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.

Nasir Razzaq

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

thomashospital

ASKER

@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.

thomashospital

ASKER

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.

thomashospital

ASKER

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

Meir Rivkin

u welcome ;-)