Connecting to and reading data from excel using VB .net

[wgg]

Hi all,


I'm in the process of developing an interface in visual basic .net to pull data from an excel spreadsheet into ingres tables. I was able to view the ingres tables via ODBC but I have not been able to connect to the Excel speadsheet from within VB.net to read the rows of data into VB objects. If you have any ideas on how to approach this, I would gladly appreciate it.

Thank you,

wgg

 

[RonRepp]

Wgg:

It depends on whether you want to connect using ODBC, as described in your post, or by using the Excel object. If you choose ODBC, it will bring a spreadsheet into your app the same as a SQL or any other ODBC method. See

http://WWW.ConnectionStrings.com

for the connection string.

Using it as an object, however, opens every Excel object to you. This can be done in 2 ways; early or late binding.

Dim XL as Object
XL = CreateObject("Excel.Application")
XL.Visible = True 'This Opens Excel
XL.Range("A1").Value = "Hello"
XL.XL.ActiveWorkbook.SaveAs Filename:= _
        Application.StartupPath & "\Documents\MyWorkbook.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
XL.Quit
''Kill the varible
XL = Nothing

Or...

''Set reference to Excelxx
Dim XL as Excel.Application = New Excel.Application
XL.Visible = True 'This Opens Excel
XL.Range("A1").Value = "Hello"
XL.ActiveWorkbook.SaveAs Filename:= _
        Application.StartupPath & "\Documents\MyWorkbook.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
XL.Quit
''Kill the varible
XL = Nothing

As you can see, they are very similar. I prefer the latter for many reasons, Intellisense being a biggie.

Do a search for Excel in this forum, use the VBA forum, or search Google for a zillion ways.

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[Craig0201]

I prefer.......

To code using the latter to get the intellisense

Then to move to the other get the flexible advantages of not being bound to a specific version of Excel.

It's only a quick refactor.

C

 

[ZmrAbdulla]

RonRepp,
Are you sure you need to create excel object ot read data from an excel file?

Just connect to it and read data to a dataset/datatable/datareader right?

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[Craig0201]

Zameer,

Good point! Was reading things in isolation.....

Overall, you're probably correct.

C

 

[ZmrAbdulla]

What I know is something different

Imports System.Data.OleDb

    Private Sub btnLoad_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnLoad.Click
        Try

            Dim MyConnection As OleDbConnection
            Dim MyDataSet As DataSet
            Dim MyCommand As OleDbDataAdapter

            MyConnection = New System.Data.OleDb.OleDbConnection _
                        ("provider=Microsoft.Jet.OLEDB.4.0; " & _
                       "data source=C:\Book1.xls; " _
                        & "Extended Properties=Excel 8.0;")

            MyCommand = New System.Data.OleDb.OleDbDataAdapter _
                        ("select * from [Sheet1$]", MyConnection)

            MyDataSet = New System.Data.DataSet
            MyCommand.Fill(MyDataSet)

            Me.DataGridView1.DataSource = MyDataSet.Tables(0)

            MyConnection.Close()


        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[wgg]

Thanks Ron Repp, Craig0201, Zameer Abdulla

I'll make a go at it now and see how it works,

Many thanks,

WGG

 

[RonRepp]

Zameer:

No, he doesn't need to set a ref to Excel, nor would he ever if he is simply using ODBC.

It depends on whether you want to connect using ODBC, as described in your post, or by using the Excel object. If you choose ODBC, it will bring a spreadsheet into your app the same as a SQL or any other ODBC method. See

http://WWW.ConnectionStrings.com

for the connection string.

I liked your post, though...very straight forward.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net