none
Make visual basic 2010 open and read excel data

    Întrebare

  • Hello Everyone,

    I am struggling big time to import my excel 2010 file to Visual Studio 2010. In Visual Studio i am using Visual Basic. I have designed the windows application form and I have added a button and a listbox where I click the button it should load the excel data into that listbox.

    I am no where and I am very lost to make this work.


    Amman Anwar

    12 martie 2012 23:36

Răspunsuri

  • Hi Amman,

    Thanks for posting in the MSDN Forum.

    It seems that you used VBA code in you VB.NET application. There have quite different between VBA and VB.NET application. I will show you a VB.NET sample. I hope it can help you.

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim objXLApp As Excel.Application
            Dim intLoopCounter As Integer
            Dim objXLWb As Excel.Workbook
            Dim objXLWs As Excel.Worksheet
            Dim objRange As Excel.Range
    
            objXLApp = New Excel.Application
            objXLApp.Workbooks.Open("*****\Book1.xlsx")
            objXLWb = objXLApp.Workbooks(1)
            objXLWs = objXLWb.Worksheets(1)
            For intLoopCounter = 1 To CInt(objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
                objRange = objXLWs.Range("A" & intLoopCounter)
                ListBox1.Items.Add(objRange.Value)
            Next intLoopCounter
    
            objXLApp.Quit()
        End Sub
    End Class

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marcat ca răspuns de Amman Anwar 15 martie 2012 18:23
    14 martie 2012 06:09

Toate mesajele

  • if you want to read data from excel to display it in your winforms app then you have following options:

    1. use OLEDB to use SQL capabilities to get data from excel

    2. use Excel Automation to read and manipualte data (require excel installed on that machine)

    3. if this if xlsx format then you can also use openxml SDK to read data - does not require excel

    13 martie 2012 08:58
  • Hello DamianD,

    I do not know how to use the OLEDB to use SQL capabilities. I have came up with a code so far? Will you be able to help me with why it is failing to compile?

    * My first step was New>project>windows application form

    * Add a listbox to the form and add a button to the form

    *The code below i added it in the properties for the button

    * This code is not running it gives me some errors that Excel.Application is not defined and List1 is not declared.

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

            Dim objXLApp As Excel.Application
            Dim intLoopCounter As Integer

            objXLApp = New Excel.Application

            With objXLApp
                .Workbooks.Open("C:\report.xls")
                .Workbooks(1).Worksheets(1).Select()

                For intLoopCounter = 1 To CInt(.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
                    List1.AddItem.Range("A" & intLoopCounter)
                Next intLoopCounter

                .Workbooks(1).Close(False)
                .Quit()
            End With

            objXLApp = Nothing
        End Sub
    End Class


    Amman Anwar

    13 martie 2012 14:23
  • It's still giving some errors. Do I have to add reference or anything?


    Amman Anwar

    13 martie 2012 17:51
  • Hi Amman,

    Thanks for posting in the MSDN Forum.

    It seems that you used VBA code in you VB.NET application. There have quite different between VBA and VB.NET application. I will show you a VB.NET sample. I hope it can help you.

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim objXLApp As Excel.Application
            Dim intLoopCounter As Integer
            Dim objXLWb As Excel.Workbook
            Dim objXLWs As Excel.Worksheet
            Dim objRange As Excel.Range
    
            objXLApp = New Excel.Application
            objXLApp.Workbooks.Open("*****\Book1.xlsx")
            objXLWb = objXLApp.Workbooks(1)
            objXLWs = objXLWb.Worksheets(1)
            For intLoopCounter = 1 To CInt(objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
                objRange = objXLWs.Range("A" & intLoopCounter)
                ListBox1.Items.Add(objRange.Value)
            Next intLoopCounter
    
            objXLApp.Quit()
        End Sub
    End Class

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marcat ca răspuns de Amman Anwar 15 martie 2012 18:23
    14 martie 2012 06:09
  • It worked for me. thanks for your help. Long live Tom.

    Amman Anwar

    15 martie 2012 18:23
  • Amman,

    Your code for the problem is good; however, it returns only data in the A column only. What if you want it to return specific data.

    For example in the excel data below, I want all the "Office plans" (Total of 6 in bold) for 8/21/2013 only to be imported into a listbox
     
    Dates                                         Office plan
    8/21/2013                     Vibration Measurement
    8/21/2013                     Wear Measurement
    8/21/2013                     Shore Measurement on EWK114CR05 - Crusher (DRS1000X2000)
    8/21/2013                     Oil Analysis on EWK175BC03 - Belt Conveyor
    8/21/2013                     First level Stopped Inspections on EWK114DC08 - Dust Collector
    8/21/2013                     Leakage Test on EWK144DG04 - Cleaning Chain of Apron Feeder
    8/22/2013                     Vibration Measurement
    8/23/2013                        Wear Measurement
    8/24/2013                        Shore Measurement on EWK114CR05 - Crusher (DRS1000X2000)
    8/25/2013                        Oil Analysis on EWK175BC03 - Belt Conveyor
    8/26/2013                        First level Stopped Inspections on EWK114DC08 - Dust Collector
    8/27/2013                        Leakage Test on EWK144DG04 - Cleaning Chain of Apron Feeder

    22 august 2013 15:17