How to Insert an Excel Spreadsheet to a VB Form

By Jaime Avelar

It's useful to know how to display a Microsoft Excel spreadsheet in a Visual Basic .NET project for further data manipulation. Microsoft Excel is a spreadsheet application included in the Microsoft Office suite. It provides many useful tools to analyze large amounts of data. Visual Basic is a programming language developed by Microsoft, and it's favored by many programmers due to its flexibility. In a few steps you can create a Visual Basic project to display contents from an Excel spreadsheet.

Step 1

Open Microsoft Excel and type "A" in "A1," "B" in "B1," "Column A" in "A2," and "Column B" in "B2." Save your spreadsheet to "C:\" as "ExcelFile.xlsx."

Step 2

Open Microsoft Visual Basic 2010 Express, click the "File" menu and select "New Project." Click "Installed Templates," select "Windows Forms Application" and click "OK."

Step 3

Press "Ctrl" + "Alt" + "X" to open the "Toolbox" window. Double-click "DataGridView" to add a new Data Grid View control to "Form1." Double-click "Button" in "Toolbox" to add a new button to "Form1."

Step 4

Double-click "Button1" to open the "Form1.vb" module. Type the following above "Public Class Form1":

Imports System.Data.OleDb

Step 5

Type the following inside "Private Sub Button1_Click" to declare a "DataSet" and define the Excel connection:

Dim ds As New DataSet()

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

    "Data Source=C:\ExcelFile.xlsx ;" & _

    "Extended Properties=Excel 12.0;"

Step 6

Type the following to connect to the "ExceFile.xlsx" file and fill the "DataSet":

Dim excelData As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString)

    excelData.TableMappings.Add("Table", "ExcelSheet")

    excelData.Fill(ds)

Step 7

Type the following to display the spreadsheet in your Data Grid View:

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

Me.Refresh()

Press "F5" to run your program and press "Button1" to import the Excel spreadsheet.

References

Writer Bio