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.


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."


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


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."


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


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;"


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)


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.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.

More Articles