Manipulating Excel Data with Visual Basic

Introduction

Knowing how to work with Microsoft Office applications, especially Excel, can prove vital in any environment. At my work, there are a few options for exporting data to the users. These options include the ever-popular PDF format, as well as Excel. PDF-based reports are read-only, whereas the Excel Reports provide more user activity. Today, I will show you how to work productively with Excel Worksheets.

Not much more to talk about, for a change! Let’s jump straight in with a project.

Start Visual Studio and create a new Visual Basic Windows Forms project. Add two buttons to your form. One button will be used to create a new Excel Worksheet and the other button will be used to Save the Excel Workbook.

Before you start with the code, you need to set a Reference to your Excel libraries on your computer. You have to keep in mind that you have to choose the correct Excel version libraries; otherwise, some of the newer features may not work and it will cause your program to break. So, find out which version of Excel is being used before you reference the wrong Excel Libraries.

Adding a Reference

  1. Click Project, Add Reference.
  2. Select the COM Tab.
  3. Scroll down until you find Microsoft Excel xx.x Object Library. In my case, it is the Microsoft Excel 15.0 Object Library.
  4. Add a checkmark next to it and select OK.

Excel1
Figure 1: Excel Object Reference

Add the following line of code to complete setting up the Excel references:

Imports Excel = Microsoft.Office.Interop.Excel

Creating a New Excel Workbook and Worksheet

Add the following code to create all the Excel objects (Excel Application, Excel Workbook, Excel Worksheet):

   Dim xlApp As New Excel.Application
   Dim xlWorkBook As Excel.Workbook
   Dim xlWorkSheet As Excel.Worksheet

Inside the New button’s code, add the following to complete the Excel object creation process:

      xlWorkBook = xlApp.Workbooks.Add

      xlApp.Visible = True

      xlWorkSheet = xlWorkBook.Sheets("Sheet1")

This instantiates an Excel Application object, adds a new Workbook, and specifies the Worksheet we will be working on. In this case, it will be Sheet1.

Adding Data to the Worksheet

Continue where you left off. In the New button’s click event code, add the following to add some data into Sheet1:

      With xlWorkSheet

         .Range("A1").Value = "Month"
         .Range("A2").Value = "January"
         .Range("A3").Value = "February"
         .Range("A4").Value = "March"
         .Range("A5").Value = "April"

         .Range("B1").Value = "Loan Repayment"
         .Range("B2").Value = "1000.00"
         .Range("B3").Value = "1200.00"
         .Range("B4").Value = "1300.00"
         .Range("B5").Value = "1600.00"

         .Range("A6").Value = "Total Paid"

         .Range("B6").Formula = "=Sum(B2:B5)"

This adds the specified text in each designated cell using the Range properties of the Worksheet object. The last line of code adds the SUM formula in cell B6. I didn’t close the With structure on purpose because we will keep adding code into it, so please keep following.

Adding Formatting to Cells

Add the following code to format our cells:

         With .Range("A1:B1")

            .Interior.ColorIndex = 4

            With .Font

               .ColorIndex = 2
               .Size = 8
               .Name = "Comic Sans MS"
               .Bold = True

            End With

         End With

         With .Range("A6:A6")

            .Interior.ColorIndex = 4

            With .Font

               .ColorIndex = 2
               .Size = 8
               .Name = "Comic Sans MS"
               .Bold = True

            End With

         End With

            .Range("B2:B6").NumberFormat = "R#,##0.00"

Cells A1 and B1 and A6 get filled in with ColorIndex 4, which is green. The font gets changed to “Comic Sans MS,” the size to 8, and it will be Bold.

Creating Borders

Add the following code to add Borders around some of your cells:

         With .Range("A1:B6")

            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)

               .LineStyle = Excel.XlLineStyle.xlDouble
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = Excel.XlBorderWeight.xlThin

            End With

            With .Borders(Excel.XlBordersIndex.xlEdgeTop)

               .LineStyle = Excel.XlLineStyle.xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = Excel.XlBorderWeight.xlThin

            End With

            With .Borders(Excel.XlBordersIndex.xlEdgeBottom)

               .LineStyle = Excel.XlLineStyle.xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = Excel.XlBorderWeight.xlThin

            End With

            With .Borders(Excel.XlBordersIndex.xlEdgeRight)

               .LineStyle = Excel.XlLineStyle.xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = Excel.XlBorderWeight.xlThin

            End With

            With .Borders(Excel.XlBordersIndex.xlInsideVertical)

               .LineStyle = Excel.XlLineStyle.xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = Excel.XlBorderWeight.xlThin

            End With

            With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)

               .LineStyle = Excel.XlLineStyle.xlContinuous
               .ColorIndex = 0
               .TintAndShade = 0
               .Weight = Excel.XlBorderWeight.xlThin

            End With

         End With

         .Columns("A:B").EntireColumn.AutoFit()

With the preceding code, I set up the borders for our two columns and their respective cells that hold data. Here is more information on Excel Border types that can be found. The very last line Auto fits all the data inside the columns. Your worksheet should look like Figure 2:

Excel2
Figure 2: The formatted worksheet

Adding a Chart

Add the following code to add a 3D Pie chart with its Legend and a proper Title:

         .Shapes.AddChart.Select()

         With xlApp.ActiveChart

            .ApplyCustomType(Excel.XlChartType.xl3DPie)
            .SetSourceData(Source:=xlWorkSheet.Range("$A$1:$B$5"))

            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _
               Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorAccent6
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.TintAndShade = 0
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.Brightness = -0.2
            xlApp.ActiveChart.ChartArea.Format.Fill.Transparency = 0
            xlApp.ActiveChart.ChartArea.Format.Fill.Solid()

            .Parent.RoundedCorners = True

            With .PlotArea

               .Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
               .Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse

            End With

            With .Legend

               With .Format.TextFrame2.TextRange.Font.Fill

                  .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                  .ForeColor.RGB = RGB(0, 0, 128)
                  .Transparency = 0
                  .Solid()

               End With

               With .Format.Fill

                  .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                  .ForeColor.ObjectThemeColor = Microsoft.Office.Core. _
                     MsoThemeColorIndex.msoThemeColorBackground2
                  .ForeColor.TintAndShade = 0
                  .ForeColor.Brightness = -0.25
                  .Transparency = 0
                  .Solid()

               End With

            End With

            .ChartTitle.Format.TextFrame2.TextRange.Font.UnderlineStyle = _
               Microsoft.Office.Core.MsoTextUnderlineType.msoUnderlineWavyLine

         End With

      End With

Your chart should resemble Figure 3:

Excel3
Figure 3: Our Chart

Saving Your Work

Add the following code to save your work:

   Private Sub btnSave_Click(sender As Object, e As EventArgs) _
      Handles btnSave.Click

      xlWorkBook.SaveAs(Filename:="C:\TEMP\Example.xlsx", _
         FileFormat:=51)

      xlWorkBook.Close()

      xlApp.Quit()

      ReleaseAll(xlApp)
      ReleaseAll(xlWorkBook)

   End Sub

   Private Sub ReleaseAll(ByVal obj As Object)

      Try
         System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
         obj = Nothing

      Catch ex As Exception

         obj = Nothing

      Finally

         GC.Collect()

      End Try

   End Sub

The previous code saves the workbooks and quits Excel properly via the use of the ReleaseAll sub that removes all the Excel references from memory.

Conclusion

It is always very handy to know how to work with external libraries, especially the libraries available in Microsoft Office. I hope you enjoyed this article as much as I did. Until next time, cheers!

Hannes DuPreez
Hannes DuPreez
Ockert J. du Preez is a passionate coder and always willing to learn. He has written hundreds of developer articles over the years detailing his programming quests and adventures. He has written the following books: Visual Studio 2019 In-Depth (BpB Publications) JavaScript for Gurus (BpB Publications) He was the Technical Editor for Professional C++, 5th Edition (Wiley) He was a Microsoft Most Valuable Professional for .NET (2008–2017).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read