locked
How to read excel in visual basic express 2008

    Question

  • Hi,

       I am creating an 'Information Library' for work using Visual Basic Express 2008, so far I have designed the forms and added the links to each form but now I am struggling.

     

    Basically I need to import data from excel into ListBoxes, labels, etc.  I know it can be done but can't quite work out how to do it.  All I need is:  Click 'Button1" which finds data in 'row 2, column 1' of excel file 'test DB.xls' and adds it to 'TextBox1'.  After that I am pretty sure I can work out the rest.  Please help.

     

    Thank you,

    Mattino

     

    PS. Before you say it, I don't have MS Access or MySql (IT won't let me add them to network!) which is why I have chosen to use Excel.

     

    Monday, April 07, 2008 12:59 PM

Answers

  • Hi Mattino,

     

    Here is a basic way to read an Excel spreadsheet into a Data Set.

     

     

    Code Snippet

    Public Class Form1

     

    Dim cn As System.Data.OleDb.OleDbConnection

    Dim cmd As System.Data.OleDb.OleDbDataAdapter

    Dim ds As New System.Data.DataSet()

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

    cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

    "data source=C:\Temp\TestDB.XLS;Extended Properties=Excel 8.0;")

     

    ' Select the data from Sheet1 of the workbook.

    cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)

     

    cn.Open()

    cmd.Fill(ds)

    cn.Close()

     

    End Sub

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     

    TextBox1.Text = ds.Tables(0).Rows(1).Item(1).ToString

     

    End Sub

     

    End Class

     

     

    When the form loads it reads the excel spreadsheet into a data set.  The button click sets the text in textbox1 to what is in row 1, column 1.

     

    Hope it helps,

    Runrin.

     

     

    Monday, April 07, 2008 2:27 PM
  • Here is another way of doing it.

    First of all, you need to add a reference to Excel. Go to Project > Add Reference and find the Microsoft Excel Object Library x.0 in the COM list. Add that. Then in your code put the following just beneath where it says Public Class Form!:

    Code Snippet

        Public objExcel As New Excel.Application


    Next you need to open the Excel Workbook, in your case, test DB.xls. Add the following statement. I recommend putting it in the Form1_Load event.

    Code Snippet

                With objExcel
                    .Visible = False 'Hides the Excel application
                    .Workbooks.Open(Application.StartupPath & "\test DB.xls")
                End With


    Note: Application.StartUpPath should really only be used when test DB.xls is being installed with your project. If you are storing it in a fixed location (ie My Documents), or are not sure how to add the .xls file to your project, let me know and I will explain how to.

    In the Button1_Click event, you will need this:

    Code Snippet

            TextBox1.Text = objExcel.Worksheets("Sheet1").range("A2").text


    Note, you will need to change 'Sheet1' to whatever your Worksheet is called. The default is Sheet1.

    And finally, create a Form1_FormClosing event and in here add the lines:

    Code Snippet

            Try
                objExcel.Quit()
            Catch ex As Exception
            End Try


    And that should do it.

    Technically, all of this can be put together in the Button1_Click event, but it will mean that there is a slight delay whenever you click the button while your program loads up Excel, retrieves the data, then closes Excel.

    Hope this helps, but if you need more help, just let me know.
    Bort
    Monday, April 07, 2008 3:08 PM

All replies

  • Hi Mattino,

     

    Here is a basic way to read an Excel spreadsheet into a Data Set.

     

     

    Code Snippet

    Public Class Form1

     

    Dim cn As System.Data.OleDb.OleDbConnection

    Dim cmd As System.Data.OleDb.OleDbDataAdapter

    Dim ds As New System.Data.DataSet()

     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

    cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

    "data source=C:\Temp\TestDB.XLS;Extended Properties=Excel 8.0;")

     

    ' Select the data from Sheet1 of the workbook.

    cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)

     

    cn.Open()

    cmd.Fill(ds)

    cn.Close()

     

    End Sub

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     

    TextBox1.Text = ds.Tables(0).Rows(1).Item(1).ToString

     

    End Sub

     

    End Class

     

     

    When the form loads it reads the excel spreadsheet into a data set.  The button click sets the text in textbox1 to what is in row 1, column 1.

     

    Hope it helps,

    Runrin.

     

     

    Monday, April 07, 2008 2:27 PM
  • Here is another way of doing it.

    First of all, you need to add a reference to Excel. Go to Project > Add Reference and find the Microsoft Excel Object Library x.0 in the COM list. Add that. Then in your code put the following just beneath where it says Public Class Form!:

    Code Snippet

        Public objExcel As New Excel.Application


    Next you need to open the Excel Workbook, in your case, test DB.xls. Add the following statement. I recommend putting it in the Form1_Load event.

    Code Snippet

                With objExcel
                    .Visible = False 'Hides the Excel application
                    .Workbooks.Open(Application.StartupPath & "\test DB.xls")
                End With


    Note: Application.StartUpPath should really only be used when test DB.xls is being installed with your project. If you are storing it in a fixed location (ie My Documents), or are not sure how to add the .xls file to your project, let me know and I will explain how to.

    In the Button1_Click event, you will need this:

    Code Snippet

            TextBox1.Text = objExcel.Worksheets("Sheet1").range("A2").text


    Note, you will need to change 'Sheet1' to whatever your Worksheet is called. The default is Sheet1.

    And finally, create a Form1_FormClosing event and in here add the lines:

    Code Snippet

            Try
                objExcel.Quit()
            Catch ex As Exception
            End Try


    And that should do it.

    Technically, all of this can be put together in the Button1_Click event, but it will mean that there is a slight delay whenever you click the button while your program loads up Excel, retrieves the data, then closes Excel.

    Hope this helps, but if you need more help, just let me know.
    Bort
    Monday, April 07, 2008 3:08 PM
  • how do you add the xls file into your application, also having input this code to my form, objexcel gives me an error of

    type system.windows.forms.application has no constructors???

    also

    Workbooks.Open gives me reference to a non shared member requires an object reference

     

     

    also i get worksheets is not a member of system.windows.forms.application

     

    any ideas why???

    Wednesday, April 30, 2008 10:22 PM
  • mat,

     

    you can copy and paste or move the file to your application directories.  or you can add a file to your project by adding an existing item in your project menu.

     

    and most likely all your errors are a result of needing a reference to excel object.

    i can't remember off hand the exact name of it but in your project menu click add reference and then search for excel object 9 or something similar.  Once you have the reference added you should be ok.

     

    Thursday, May 01, 2008 2:49 AM
  • Hi Mat,

    I agree with JS06. It sounds like you need to add the Excel object library to your project. Go to the Project menu and click Add Reference. Select the COM tab along the top, then find the entry that reads 'Microsoft Excel x.0 Object Library' where x represents your version of Excel. Select it and click accept.

    To add your .xls file to the project, in the Solution Explorer, right click on your project name select Add and in the new menu that opens, select Existing Item. Find your .xls file in the new window and click OK. Once this is done, go to the Project menu, and select <Project> Properties where <Project> is the name of your project.

    In the Properties page that opens, select the Publish tab at the bottom, then click Application Files. You should see your .xls file listed here now, but if not, click the Show All Files checkbox. Find your .xls file in the list and make sure that it's Publish Status is set to Include and it's Download Group is set to Required.

    Also, for testing, copy the .xls file into the bin\debug folder in your project folder.

    Once all this is done, you should be ready to use the .xls file in your project. It will be installed with the rest of your project when you publish it, and in your code, you can reference it with Application.StartupPath & "\XLSFILENAME.xls".

    Hope this helps, but if you need any more info, just let me know.

    Bort
    Thursday, May 01, 2008 8:22 AM
  • o.k i can now see the xls file in the solution explorer, but cant see it in the xls file in the publish list even when checking show all files???

     

    Thursday, May 01, 2008 11:42 PM
  • Oops, sorry Mat. My bad. In the Solution Explorer, click the .xls file. Now in the Properties window, change the Build Action to Content. That should fix it, and you should then be able to see it in the Publish list.
    Friday, May 02, 2008 8:32 AM
  • Instead of hardcoding of the filename, is there any way to make it as dynamic, such as getting input file name from a textbox input or chosing from a file selection dialog box and then accessing the excel file data and displaying the data of certain column in a listbox??????? 

     

    Thursday, May 29, 2008 6:39 AM
  • Hi Inderjith,

    Getting the filename from a TextBox is pretty easy. Simply change this:

    Code Snippet

                    .Workbooks.Open(Application.StartupPath & "\test DB.xls")


    To this:

    Code Snippet

                .Workbooks.Open(Application.StartupPath & "\" & txtFilename.Text & ".xls")


    Displaying the data in a ListBox can be done like this:

    Code Snippet

        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

            oExcel = CType(CreateObject("Excel.Application"), Excel.Application)
            oBook = oExcel.Workbooks.Open(Application.StartupPath & "\" & TextBox5.Text & ".xlt")

            Dim cell As Integer = 1

            Do Until oExcel.Range("A" & cell.ToString).Value Is Nothing
                ListBox1.Items.Add(oExcel.Range("A" & cell.ToString).Value.ToString)
                cell += 1
            Loop

        End Sub


    If you want a different column displayed, just change "A" to whatever column you need.

    And finally, selecting the Excel file from a dialog box. Put this above the "oExcel = " line in the snippet above.

    Code Snippet

            OpenFileDialog1.Filter = "Excel Files (*.xls)|*.xls"

            If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                TextBox5.Text = OpenFileDialog1.FileName
            End If


    And don't forget to put the OpenFileDialog on the form in the Design window.

    Hope all this helps,
    Bort
    Thursday, May 29, 2008 10:26 AM
  • Hi,

    you can easily VB.NET read Excel with this C# / VB.NET Excel component.

    Here is a sample VB.NET code:

    Dim ef As New ExcelFile
     
    ' Loads Excel file.
    ef.LoadXls("filename.xls")
     
    ' Selects first worksheet.
    Dim ws As ExcelWorksheet = ef.Worksheets(0)
     
    ' Displays the value of first cell in the messageBox.
    MessageBox.Show(ws.Cells("A1").Value.ToString())

    Tuesday, April 17, 2012 7:31 AM