?
Solved

Read an EXCEL file in VB.NET

Posted on 2006-06-08
11
Medium Priority
?
2,558 Views
Last Modified: 2008-10-04
I need to read an excel file and then load it into a SQL table

The SQL stuff I have done many time

Never read an excel file in VB.NET

Could someone show me how it is done
0
Comment
Question by:SynapcoCanada
  • 3
  • 3
  • 2
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16861803
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16862005
Function ReadDataFromExcel(ByVal excelfilename As String) As DataSet
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection
        Try

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & excelfilename & "; " & _
                  "Extended Properties=Excel 8.0;")

            da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn)

            conn.Open()

            da.Fill(ds)


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try

    End Function
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16862014
don't forget to import System.Data.OleDb
0
Independent Software Vendors: 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!

 

Author Comment

by:SynapcoCanada
ID: 16862353
Getting an error from [Sheet1$]

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16862372
>>[Sheet1$]

Have you tried replacing this with your actual worksheet name?
0
 

Author Comment

by:SynapcoCanada
ID: 16862435
I know nothing about Excel. Where do you find the worksheet name?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16862453
If you open your excel file in Excel, there are tabs at the bottom left. There are names into tabs.
0
 
LVL 35

Accepted Solution

by:
YZlat earned 1000 total points
ID: 16864368
small correction:

Function ReadDataFromExcel(ByVal excelfilename As String) As DataSet
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection
        Try

            conn = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & excelfilename & "; " & _
                  "Extended Properties=Excel 8.0;")

            da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn)

            conn.Open()

            da.Fill(ds)


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
      ReadDataFromExcel=ds
    End Function
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Simple Linear Regression

850 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