Make visual basic 2010 open and read excel data

Question

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

Answer 1

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

Answer 2

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

Answer 3

try to search somewhat on the internet, there is plethora of info about this. Examples:

http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm

http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB

Answer 4

It's still giving some errors. Do I have to add reference or anything?

---

Amman Anwar

Answer 5

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

Answer 6

It worked for me. thanks for your help. Long live Tom.

---

Amman Anwar