Creating an Excel Application in VB.NET

Submitted by: 
Visitors have accessed this post 1991 times.

In this tutorial, we will create an Excel Application.

Excel is a spreadsheet application developed by Microsoft. Excel features calculation, graphing tools, pivot tables, and a macro programming language. Thus, excel is really important in offices for making reports, request letter, and for some miscellaneous applications.

This tutorial uses Microsoft.Office.Interop.Excel in our Windows Form Application.

Let's start with creating a Windows Form Application for this tutorial by following the following steps in Microsoft Visual Studio: Go to File, click New, and choose Windows Form Application.

Finally, select OK, then it creates your project and displays following Form1.

Insert a Button control named Button1 in the form.

Add a reference to Microsoft Excel Object Library to your project. To do this follow the image below. Note: this is really an important library to add an excel file.

Add reference
On the COM tab, locate Microsoft Excel Object Library and then click Select Microsoft Excel 14.0 Object Library
. Then Click OK. Follow this image below.

design reference

Your design must be look like this:
design

Double click the code window and choose the Click event of Button1 and write the following code below.

  1. Imports Microsoft.Office.Interop.Excel
  2.  
  3. Public Class Form1
  4. Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
  5. Dim appXL As Application
  6. Dim wbXl As Workbook
  7. Dim shXL As Worksheet
  8. Dim raXL As Range
  9. ' Start Excel and get Application object.
  10. appXL = CreateObject("Excel.Application")
  11. appXL.Visible = True
  12. ' Add a new workbook.
  13. wbXl = appXL.Workbooks.Add
  14. shXL = wbXl.ActiveSheet
  15. ' Add table headers going cell by cell.
  16. shXL.Cells(1, 1).Value = "First Name"
  17. shXL.Cells(1, 2).Value = "Last Name"
  18. shXL.Cells(1, 3).Value = "Full Name"
  19. shXL.Cells(1, 4).Value = "Specialization"
  20. ' Format A1:D1 as bold, vertical alignment = center.
  21. With shXL.Range("A1", "D1")
  22. .Font.Bold = True
  23. .VerticalAlignment = XlVAlign.xlVAlignCenter
  24. End With
  25. ' Create an array to set multiple values at once.
  26. Dim students(5, 2) As String
  27. students(0, 0) = "Lyndon"
  28. students(0, 1) = "Bermoy"
  29. students(1, 0) = "Novee"
  30. students(1, 1) = "Dumanig"
  31. students(2, 0) = "Aga"
  32. students(2, 1) = "Bermoy"
  33. students(3, 0) = "Don"
  34. students(3, 1) = "Bermzkiee"
  35. students(4, 0) = "Sourcecodester"
  36. students(4, 1) = "TheBest"
  37. ' Fill A2:B6 with an array of values (First and Last Names).
  38. shXL.Range("A2", "B6").Value = students
  39. ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
  40. raXL = shXL.Range("C2", "C6")
  41. raXL.Formula = "=A2 & "" "" & B2"
  42. ' Fill D2:D6 values.
  43. With shXL
  44. .Cells(2, 4).Value = "Programming"
  45. .Cells(3, 4).Value = "Mechatronics"
  46. .Cells(4, 4).Value = "Robotics"
  47. .Cells(5, 4).Value = "Mathmematics"
  48. .Cells(6, 4).Value = "Best Website"
  49. End With
  50. ' AutoFit columns A:D.
  51. raXL = shXL.Range("A1", "D1")
  52. raXL.EntireColumn.AutoFit()
  53. ' Make sure Excel is visible and give the user control
  54. ' of Excel's lifetime.
  55. appXL.Visible = True
  56. appXL.UserControl = True
  57. ' Release object references.
  58. raXL = Nothing
  59. shXL = Nothing
  60. wbXl = Nothing
  61. appXL.Quit()
  62. appXL = Nothing
  63. Exit Sub
  64. Err_Handler:
  65. MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
  66. End Sub
  67. End Class

Then run the application and click Button1. The excel will display based on the code on your Button1. It will look like this:

Excel

Then click Save, to save the Excel File.

Download the source code below and try it! :)

For more inquiries and need programmer for your thesis systems in any kind of programming languages, just contact my number below.

Best Regards,

Engr. Lyndon R. Bermoy

IT Instructor/System Developer/Android Developer
STI College - Surigao City
Mobile: 09488225971
E-mail:[email protected]

Follow and add me in my Facebook Account: https://www.facebook.com/donzzsky


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.