VB.NET and Excel

EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/vb-dot-net-and-excel/

 

This section is specifically dedicated to VB.Net and Excel.

• VB.Net Version: 2010 Ultimate
• Excel Version: Excel Professional Plus 2010

We will be covering the following. In case you feel that I should be covering something besides the below then do drop me a line and I will include that

Note: The below section takes you step by step and then in point 24 shows how to close and clean up. Before you start using the Samples from below, I would recommend you to read this first.

1. Setting up VB.Net to Work with Excel
2. Creating a New Excel File
3. Opening an Excel File
4. Writing some text to Excel File
5. Inserting Formulas
6. Formatting Text
7. Creating/Formatting a Table
8. Creating/Formatting a Chart
9. Creating/Formatting a Pivot Table
10. Sorting Data
11. Grouping/UnGrouping Data
12. Subtotals
13. Filtering a Range – Adding AutoFilter
14. Checking if a Sheet Exists
15. Adding/Deleting Sheets to the Excel File
16. Copying and Pasting Range
17. Merging/Unmerging Cells
18. Adding/Deleting Shapes in a worksheet
19. Page Setup in Excel
20. Printing Excel Sheet/Workbook
21. Protecting/Unprotecting a Worksheet/Workbook
22. Saving the Excel File (Save / Save As Method)
23. Mailing the Excel file using Outlook
24. Close Excel and Clean Up (Also see this)
    
25. Find and Replace in Excel using VB.Net (Added on 16th Oct 2011)
26. VB.Net to Read and Set Excel’s Inbuilt Document Properties (Added on 19th Jan 2012)
27. Fill/Retrieve data from PDF Form Fields using VB.Net From an Excel File (Added on 28th Jan 2012)
28. Calling Excel Macros programmatically in VB.Net (Added on 20th Mar 2012)
29. VB.Net – Interacting with Comments in Excel Cells (Added on 28th Apr 2012)
30. VB.Net/VBA Copy Rows From Multiple Tabs Into One Sheet in Excel (Added on 5th Jun 2012)
31. Excel Text To Columns From VB.net (Added on 29th Jun 2012)

---

Setting up VB.Net to Work with Excel

---

Click on File and click on New Project. Under Visual basic, Select “Windows” and then select “Windows Form Application”. Give the project an appropriate name and select a location where you want to save this project.

When the project opens you will be presented with Form1. For the time being just lets create 3 buttons and name then “Create”, “Open”, “Cancel”. Give an appropriate heading to the form. Your form should look like this.

Now let’s set references for Excel in our project. For this click on the menu Project~~>Add Reference. You will be presented with a “Add Reference” dialog box. Navigate to the “COM” Tab and select the Microsoft Excel Object Library.

After setting the reference, double click on any of the button to open the Form1 Code Area and paste this line of code at the very top.

Imports Excel = Microsoft.Office.Interop.Excel

Double click on the third button “Cancel” and paste the code. This is how your code should look like.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

End Class

And you are all set for automating Excel From VB.Net.

---

Creating a New Excel File

---

Double click the first button “Create” and paste this code. I will be commenting the code so that can understand what the code does

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

End Class

Now when you run the project, your form will launch and when you click on “Create” button, Excel will launch and a new workbook will be created.

---

Opening an Excel File

---

If you want to open an existing Excel file then we will not use .Add method. Instead we will use the .Open method. Double click on the “Open” button and paste the code given below. This is how your code will look when you want to open an Excel file.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

End Class

Now when you run the project, your form will launch and when you click on “Open” button, Excel will launch and the relevant workbook will open.

---

Writing some text to Excel File

---

Now let’s try writing something to the Excel File. In order to do so, we need to first define the Excel worksheet object so that we can interact with it. Once that is done will will write to column A and Col B. Let’s say the data that we want to put in Excel is as follows

Month	Money Spent
January	1000.00
February	1500.00
March	1200.00
April	1100.00

In the code below I will be creating a new file and then use code to populate the above data in Excel. So obviously the code goes in the first button “Create”. If you choose to, you can also put the data in an existing workbook using the 2nd button “Open”

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"
        End With
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

End Class

Now when you run the project, your form will launch and when you click on “Create” button, Excel will launch and a new workbook will be created. You will see the data in the Excel Sheet. See image below.

---

Inserting Formulas

---

Now let’s say we want to find out the total expenses that we had from Jan to April. What If we could also find what was out average spend? In this section we will find the total expenses and display it in row 6 and the average expense in row 7. See this code. I am just pasting the code for the click event of Button1 as the other parts remains unchanged.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"
        End With
    End Sub

When you run the code, this is the output that you will get.

---

Formatting Text

---

Now let’s try and make our data above more presentable.

The first thing that we notice that the text in the cells going beyond the cell columns. So let’s fix that. In fact the following are few of the things that we will be doing to make our data presentable.

1. Autofit Columns
2. Shading the TITLES (Month, Money Spent, Total Expenses and Average Expenses”) to Black
3. Change the font color of the title to White
4. Formatting the amount to display as $####.##
5. And finally create broders

See this code.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"

            '~~> Shade the titles
            With .Range("A1:B1,A6:A7")
                .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
                With .Font
                    .ColorIndex = 2 '<~~ Font Color White
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With

            End With

            '~~> Set the number format
            .Range("B2:B7").NumberFormat = "$#,##0.00"

            '~~> Create Borders
            With .Range("A1:B7")
                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

            '~~> Autofitting text in columns
            .Columns("A:B").EntireColumn.AutoFit()
        End With
    End Sub

When you run the code, this is the output that you will get.

---

Creating/Formatting a Table

---

What we were doing till now is putting the data in Excel and then formatting the data to look like a presentable table. Now let’s try and create the table using Excel’s inbuilt Table creating facility.

See this code.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"

            '~~> Change the range into a tabular format
            .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

            '~~> Format the table
            .ListObjects("Table1").TableStyle = "TableStyleLight8"

            '~~> Format the Total and Average Expenses cells
            With .Range("A6:A7")
                .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
                With .Font
                    .ColorIndex = 2 '<~~ Font Color White
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            '~~> Autofitting text in columns
            .Columns("A:B").EntireColumn.AutoFit()
        End With
    End Sub

If you notice, Excel creates dropdown buttons. The main advantage of this dropdown button is that you can sort or filter the data as per your requirements. See image below.

---

Creating/Formatting a Chart

---

Now we have our data all setup in the Excel file. It even looks presentable. What if we could also show a graph which depicts the expenses for the months. Wouldn’t that be wonderful?

Consider this code. This will create a Line Graph next to the data.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"

            '~~> Change the range into a tabular format
            .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

            '~~> Format the table
            .ListObjects("Table1").TableStyle = "TableStyleLight8"

            '~~> Format the Total and Average Expenses cells
            With .Range("A6:A7")
                .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
                With .Font
                    .ColorIndex = 2 '<~~ Font Color White
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            '~~> Autofitting text in columns
            .Columns("A:B").EntireColumn.AutoFit()

            '~~> Inserting a Graph
            .Shapes.AddChart.Select()
            xlApp.ActiveChart.ApplyCustomType(Excel.XlChartType.xlLineMarkers)
            xlApp.ActiveChart.SetSourceData(Source:=.Range("Sheet1!$A$1:$B$5"))
        End With
    End Sub

And this is the output that we will get.

What if we want to format the Chart and change it’s properties so that the chart looks like this?

This code will help us achieve it

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January"
            .Range("A3").Value = "February"
            .Range("A4").Value = "March"
            .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00"
            .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00"
            .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"

            '~~> Change the range into a tabular format
            .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

            '~~> Format the table
            .ListObjects("Table1").TableStyle = "TableStyleLight8"

            '~~> Format the Total and Average Expenses cells
            With .Range("A6:A7")
                .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
                With .Font
                    .ColorIndex = 2 '<~~ Font Color White
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            '~~> Autofitting text in columns
            .Columns("A:B").EntireColumn.AutoFit()

            '~~> Inserting a Graph
            .Shapes.AddChart.Select()

            '~~> Formatting the chart
            With xlApp.ActiveChart
                '~~> Make it a Line Chart
                .ApplyCustomType(Excel.XlChartType.xlLineMarkers)

                '~~> Set the data range
                .SetSourceData(Source:=xlWorkSheet.Range("$A$1:$B$5"))

                '~~> Fill the background of the chart
                xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _
                Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorBackground1 '<~~ Grey
                xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.TintAndShade = 0
                xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.Brightness = -0.150000006
                xlApp.ActiveChart.ChartArea.Format.Fill.Transparency = 0
                xlApp.ActiveChart.ChartArea.Format.Fill.Solid()

                '~~> Make the corners of the Chart Rount
                .Parent.RoundedCorners = True

                '~~> Removing lines and the back color so plot area shows char's background color
                With .PlotArea
                    .Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
                    .Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
                End With

                '~~> Removing the major gridlines
                .Axes(Excel.XlAxisType.xlValue).MajorGridlines.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse

                '~~> Making the series line smooth
                .SeriesCollection(1).Smooth = True

                '~~> Formatting the legend
                With .Legend
                    With .Format.TextFrame2.TextRange.Font.Fill
                        .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                        .ForeColor.RGB = RGB(255, 0, 0)
                        .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

                '~~> Chnage the format of Y axis to show $ signs
                .Axes(Excel.XlAxisType.xlValue).TickLabels.NumberFormat = "$#,##0.00"

                '~~> Underline the Chart Title
                .ChartTitle.Format.TextFrame2.TextRange.Font.UnderlineStyle = _
                Microsoft.Office.Core.MsoLineStyle.msoLineSingle
            End With
        End With
    End Sub

---

Creating/Formatting a Pivot Table

---

Now we have learned how to create graph based on the data that we have on Excel. Now let’s try and create a pivot table based on the same data. The below code will create a pivot table in the same worksheet in Cell D1.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January" : .Range("A3").Value = "February"
            .Range("A4").Value = "March" : .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00" : .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00" : .Range("B5").Value = "1100.00"

            '~~> Set title for Total and Average Expenses
            .Range("A6").Value = "Total Expense"
            .Range("A7").Value = "Average Expense"

            '~~> Insert formulas
            .Range("B6").Formula = "=Sum(B2:B5)"
            .Range("B7").Formula = "=Average(B2:B5)"

            '~~> Change the range into a tabular format
            .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

            '~~> Format the table
            .ListObjects("Table1").TableStyle = "TableStyleLight8"

            '~~> Format the Total and Average Expenses cells
            With .Range("A6:A7")
                .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
                With .Font
                    .ColorIndex = 2 '<~~ Font Color White
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
            End With

            '~~> Autofitting text in columns
            .Columns("A:B").EntireColumn.AutoFit()

            Dim wbname As String = xlWorkBook.Name

            '~~> Create and setup a pivot cache
            Dim ptCache As Excel.PivotCache = xlWorkBook.PivotCaches.Add( _
            SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:= _
            .Range("$A$1:$B$5"))

            '~~> Create a pivot table
            Dim ptTable As Excel.PivotTable = .PivotTables.add(PivotCache:=ptCache, _
            TableDestination:=.Range("D1"), TableName:="My_Pivot_Table")

            '~~> Setup/Format the pivot table
            With ptTable
                .ManualUpdate = True
                '~~> Putting month in the Row Field
                .PivotFields("Month").Orientation = Excel.XlPivotFieldOrientation.xlRowField
                '~~> Select a formatting for the pivot
                .Format(Excel.XlPivotFormatType.xlReport1)
                .ManualUpdate = False
            End With

            '~~> Sum the amount spent
            ptTable.AddDataField(.PivotTables("My_Pivot_Table").PivotFields("Money Spent"), _
            "Sum of Money Spent", Excel.XlConsolidationFunction.xlSum)

            '~~> Format the Money Spent in the pivot to reflect in $
            ptTable.PivotFields("Sum of Money Spent").NumberFormat = "$#,##0.00"

        End With
    End Sub

When you run the data, the pivot will look like this.

You can change the way your pivot table looks by changing the xlReport1 in

.Format(Excel.XlPivotFormatType.xlReport1)

---

Sorting Data

---

There are times when you need to sort your data to a clearer representation. Depending upon your requirements, you might want to sort the data in ascending order or descending order. I have covered them both.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month"
            .Range("A2").Value = "January" : .Range("A3").Value = "February"
            .Range("A4").Value = "March" : .Range("A5").Value = "April"

            .Range("B1").Value = "Money Spent"
            .Range("B2").Value = "1000.00" : .Range("B3").Value = "1500.00"
            .Range("B4").Value = "1200.00" : .Range("B5").Value = "1100.00"

            '~~> To sort Ascending
            .Range("A1:B5").Sort(Key1:=.Range("B2"), Order1:=Excel.XlSortOrder.xlAscending, Header:=Excel.XlYesNoGuess.xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=Excel.XlSortOrientation.xlSortColumns, _
            DataOption1:=Excel.XlSortDataOption.xlSortNormal)

            '~~> To sort Descending change xlAscending to xlDescending above

            '~~> Autofitting text in columns
            .Columns("A:B").EntireColumn.AutoFit()
        End With
    End Sub

---

Grouping/UnGrouping Data

---

To understand this example, let’s take a bigger dataset. For example

Month	Date	Money Spent
January	1/6/2011	200
January	1/10/2011	350
January	1/20/2011	155
January	1/31/2011	345
February	2/6/2011	213
February	2/10/2011	231
February	2/20/2011	321
February	2/31/2011	341
March	3/6/2011	231
March	3/10/2011	345
March	3/20/2011	432
March	3/31/2011	124
April	4/6/2011	543
April	4/10/2011	342
April	4/20/2011	222
April	4/31/2011	215

So if we ran the below code, we will get an output as shown in the image below.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
            .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
            .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

            .Range("B2").Value = "1/6/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
            .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
            .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
            .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
            .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
            .Range("B17").Value = "1/31/2011"

            .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
            .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
            .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
            .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
            .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
            .Range("C17").Value = "215"

            '~~> Autofitting text in columns
            .Columns("A:C").EntireColumn.AutoFit()
        End With
    End Sub

This is a very simple dataset but good enough to achieve what we want. Sometimes when we have huge dataset, we Hide/Show Columns/Rows by right clicking on them and then clicking on hide to see the relevant data. But there is another feature in Excel; Grouping and Ungrouping. If you were in Excel then all you need is to select cells which you want to group and then  Group > Rows or Cells from the Data Tab in Excel 2010.

Now suppose we want to group data on the basis of months. So this is how data will look like after grouping. You can click on the +/- signs to show hide your data

To achieve this, we can use this code.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
            .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
            .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

            .Range("B2").Value = "1/6/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
            .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
            .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
            .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
            .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
            .Range("B17").Value = "1/31/2011"

            .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
            .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
            .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
            .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
            .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
            .Range("C17").Value = "215"

            '~~> Insert rows to create header for month
            .Range("6:6,10:10,14:14").Insert(Shift:=Excel.XlDirection.xlDown, _
            CopyOrigin:=Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)

            '~~> Create Month Headers
            .Range("A6").Value = "January" : .Range("A10").Value = "February"
            .Range("A14").Value = "March" : .Range("A21").Value = "April"

            '~~> Group the relevant rows
            .Rows("2:5").Rows.Group()
            .Rows("7:10").Rows.Group()
            .Rows("12:15").Rows.Group()
            .Rows("17:20").Rows.Group()

            '~~> Autofitting text in columns
            .Columns("A:C").EntireColumn.AutoFit()
        End With
    End Sub

---

Creating Subtotals

---

What if the above “Grouping” could also show us the Total or the Average amount that we spent in each month?

Either we can follow the above approach to ‘Group’ the data and use Excel’s formula “=Subtotal()” or we use the Excel’s inbuilt feature to create the subtotal automatically

Consider this Example

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
            .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
            .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

            .Range("B2").Value = "1/6/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
            .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
            .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
            .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
            .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
            .Range("B17").Value = "1/31/2011"

            .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
            .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
            .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
            .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
            .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
            .Range("C17").Value = "215"

            '~~> Creating the Subtotal. {3} below is the number of columns i.e from A to C
            '~~> To find the vaerage use xlAverage instead of xlSum
            .Range("A1:C17").Subtotal(GroupBy:=1, Function:=Excel.XlConsolidationFunction.xlSum, TotalList:=New Int32() {3}, _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True)

            '~~> Autofitting text in columns
            .Columns("A:C").EntireColumn.AutoFit()
        End With
    End Sub

This is the output that you will get.

---

Filtering a Range – Adding AutoFilter

---

Sometime you need to just add Autofilter in your data so that you can filter the data based on multiple conditions. In short it lets you customize you filtering criteria.

See this image.

The above can be achieved by this code.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the relebant sheet that we want to work with
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet
            '~~> Directly type the values that we want
            .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
            .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
            .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

            .Range("B2").Value = "1/6/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
            .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
            .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
            .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
            .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
            .Range("B17").Value = "1/31/2011"

            .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
            .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
            .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
            .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
            .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
            .Range("C17").Value = "215"

            '~~> Add Autofilter to the header range
            .Range("A1:C17").AutoFilter(Field:=1, Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

            '~~> Autofitting text in columns
            .Columns("A:C").EntireColumn.AutoFit()
        End With
    End Sub

If you want you can also specify the filter options in your code to directly filter the data. For example, if you want to show only records form “January” then see this code below.

Change the line

            '~~> Add Autofilter to the header range
            .Range("A1:C17").AutoFilter(Field:=1, Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

to

            '~~> Add Autofilter to the header range
            .Range("A1:C17").AutoFilter(Field:=1, Criteria1:="January", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

And you will get this as your result.

You can also filter it to show data from “January” where the “Money Spent” is less than say 300. For that you will change the code to this.

            '~~> Add Autofilter to the header range
            .Range("A1:C17").AutoFilter(Field:=1, Criteria1:="January", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)
            .Range("A1:C17").AutoFilter(Field:=3, Criteria1:="<300", Operator:=Excel.XlAutoFilterOperator.xlFilterValues)

This is the result that you will get.

---

Checking if a Sheet Exists

---

Sometimes you need to know if a particular sheet is there in Excel. I am showing you two ways to check that. the first way is to directly check for the existence of that sheet and the second way is to create a function so that you can use it again and again if required. For this example we will be using the .Open Method to open an existing workbook and then checking for the sheets in that workbook. Lets say we have a workbook “Sample.xlsx” which looks like this

And we are supposed to check if the workbook has a sheet called “Sample”

Way 1

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim SheetNameToCheck As String = "Sample"
        Dim xs As Excel.Worksheet

        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Loop through the all the sheets in the workbook to find if name matches
        For Each xs In xlWorkBook.Sheets
            If xs.Name = SheetNameToCheck Then
                MessageBox.Show("The sheet " & SheetNameToCheck & " found.")
            End If
        Next
    End Sub

Way 2

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim SheetNameToCheck As String = "Sample"

        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Check If sheet exists using a function
        If DoesSheetExists(SheetNameToCheck) Then
            MessageBox.Show("The sheet " & SheetNameToCheck & " found.")
        Else
            MessageBox.Show("The sheet " & SheetNameToCheck & " not found.")
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    Public Function DoesSheetExists(ByVal shtName As String) As Boolean
        Dim xs As Excel.Worksheet

        DoesSheetExists = False

        '~~> Loop through the all the sheets in the workbook to find if name matches
        For Each xs In xlWorkBook.Sheets
            If xs.Name = shtName Then
                DoesSheetExists = True
            End If
        Next
    End Function

End Class

The above function will be helpful if you are planning to check the names of many sheets. This way you will not have to write the code for looping and checking that many times.

---

Adding/Deleting Sheets to the Excel File

---

Adding and Deleting sheets in Excel is very simple.

Consider this code. I am also using the DoesSheetExist Function from above in this example to demonstrate the naming of a sheet when you add a new sheet.

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim NewSheetName As String = "MyNewSheet"

        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Add a new Sheet
        xlWorkSheet = xlWorkBook.Sheets.Add

        '~~> Check If "MyNewSheet" sheet exists using a function and it doesn't exist then rename the
        '~~> above added sheet to "MyNewSheet"
        If DoesSheetExists(NewSheetName) Then
            MessageBox.Show("The sheet " & NewSheetName & " already exists. Please select another name.")
        Else
            xlWorkSheet.Name = NewSheetName
        End If
    End Sub

    Public Function DoesSheetExists(ByVal shtName As String) As Boolean
        Dim xs As Excel.Worksheet

        DoesSheetExists = False

        '~~> Loop through the all the sheets in the workbook to find if name matches
        For Each xs In xlWorkBook.Sheets
            If xs.Name = shtName Then
                DoesSheetExists = True
            End If
        Next
    End Function

To delete sheet, use this code.

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim NewSheetName As String = "MyNewSheet"

        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Add a new Sheet
        xlWorkSheet = xlWorkBook.Sheets.Add

        '~~> Check If "MyNewSheet" sheet exists using a function and it exists then delete it
        If DoesSheetExists(NewSheetName) Then
            xlWorkSheet.Delete()
        Else
            MessageBox.Show("The sheet " & NewSheetName & " Doesn't exists. Please select another name.")
        End If
    End Sub

    Public Function DoesSheetExists(ByVal shtName As String) As Boolean
        Dim xs As Excel.Worksheet

        DoesSheetExists = False

        '~~> Loop through the all the sheets in the workbook to find if name matches
        For Each xs In xlWorkBook.Sheets
            If xs.Name = shtName Then
                DoesSheetExists = True
            End If
        Next
    End Function

---

Copying and Pasting Range

---

There are many times when you want to copy a range from one area to another or from one worksheet to another. I would be covering these 3 scenarios.

• Copy range from one area to another in the same worksheet
• Copy range from one one worksheet to another worksheet in the same workbook
• Copy range from one one worksheet to another worksheet in a different workbook

To begin with put a 4th button on your form and name it “Copy Range” as shown in the picture below.

We will be using this button for copy and pasting the range.

Scenario 1 : Copy range from one area to another in the same worksheet

Let’s say we want to copy a range A1 to B10 in Sheet1 to range D1 to E10. See this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A1:B10")

        '~~> Set the destination range
        xlDestRange = xlWorkSheet.Range("D1")

        '~~> Copy and paste the range
        xlSourceRange.Copy(xlDestRange)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

Scenario 2 : Copy range from one one worksheet to another worksheet in the same workbook

Now if we want to copy a range A1 to B10 from Sheet1 to A1 in Sheet2 then use this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens an exisiting Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        '~~> Set the destination worksheet
        xlWsheet2 = xlWorkBook.Sheets("Sheet2")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A1:B10")
        '~~> Set the destination range
        xlDestRange = xlWsheet2.Range("A1")

        '~~> Copy and paste the range
        xlSourceRange.Copy(xlDestRange)
    End Sub
End Class

When you run this, you will notice that the range is now copied successfully.

Scenario 3 : Copy range from one one worksheet to another worksheet in a different workbook

Now let’s say we want to copy a range A1 to B10 from Sheet1 in Sample.xlsx to A1 in Sheet1  of Book1.xlsx. See this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook, xlWorkBook2 As Excel.Workbook
    Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet
    Dim xlSourceRange, xlDestRange As Excel.Range

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Opens Destination Workbook. Change path and filename as applicable
        xlWorkBook2 = xlApp.Workbooks.Open("C:\Tutorial\Book1.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        '~~> Set the destination worksheet
        xlWsheet2 = xlWorkBook2.Sheets("Sheet1")

        '~~> Set the source range
        xlSourceRange = xlWorkSheet.Range("A1:B10")
        '~~> Set the destination range
        xlDestRange = xlWsheet2.Range("A1")

        '~~> Copy and paste the range
        xlSourceRange.Copy(xlDestRange)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

---

Merging/Unmerging Cells

---

Here is an example to merge cells A1 to E1.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects Dim xlApp As New Excel.Application Dim xlWorkBook, xlWorkBook2 As Excel.Workbook Dim xlWorkSheet, xlWsheet2 As Excel.Worksheet Dim xlSourceRange, xlDestRange As Excel.Range Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel xlApp.Visible = True '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Set the source range xlSourceRange = xlWorkSheet.Range("A1", "E1") '~~> Merge the range
        xlSourceRange.Merge()

        '~~> If you want center alignment of the text horizontly and vertically then use this
        xlSourceRange.HorizontalAlignment = Excel.Constants.xlCenter
        xlSourceRange.VerticalAlignment = Excel.Constants.xlCenter
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

---

Adding/Deleting Shapes in a worksheet

---

You can add shapes using the below code. The syntax is

Sheet.Shapes.AddShape(Shape Type, Left, Top, Width, Height).Select()

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> This will insert a rectangular shape.
        xlWorkSheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 105.75, 54.75, 114, 65.25).Select()

        '~~> This will insert an oval shape
        xlWorkSheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeOval, 441, 57, 117.75, 90.75).Select()

        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        ' similarly you can use msoShapeSmileyFace to insert a smiley face shape '
        ' and msoShapeHeart to insert aHeart shape. the moment you type '
        ' Microsoft.Office.Core.MsoAutoShapeType and type a DOT (.), you will get'
        ' a list of all the shapes that you can draw '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

To delete the shape that we created use this

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Deleting the Shapes
        xlWorkSheet.Shapes.Range(New String() {"Oval 2"}).Delete()
        xlWorkSheet.Shapes.Range(New String() {"Rectangle 1"}).Delete()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

For the above method you need to know the name of the shape. In case you want to delete all the shapes in the worksheet then use this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim shp As Excel.Shape

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Loop through all the shapes in the worksheet and delete them
        For Each shp In xlWorkSheet.Shapes
            shp.Delete()
        Next
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

---

Page Setup in Excel

---

Once you have the data all set in your worksheet, you might want to tweak a few settings in the page setup before the document can be printed. Here is the code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        With xlWorkSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With

        With xlWorkSheet.PageSetup
            '~~> In this section, you can define where and what your header 
            '~~> and footer should look like
            .LeftHeader = ""
            .CenterHeader = "Sample Header"
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = "Sample Footer"
            .RightFooter = ""

            '~~> In this section, you can set the margins
            .LeftMargin = xlApp.InchesToPoints(0.45)
            .RightMargin = xlApp.InchesToPoints(0.45)
            .TopMargin = xlApp.InchesToPoints(0.5)
            .BottomMargin = xlApp.InchesToPoints(0.5)
            .HeaderMargin = xlApp.InchesToPoints(0.05)
            .FooterMargin = xlApp.InchesToPoints(0.05)

            '~~> Other settings that you can set
            .PrintHeadings = True
            .PrintGridlines = True
            .PrintComments = Excel.XlPrintLocation.xlPrintNoComments
            .PrintQuality = 1200
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = Excel.XlPageOrientation.xlLandscape
            .Draft = True
            .PaperSize = Excel.XlPaperSize.xlPaperA4
            .FirstPageNumber = Excel.Constants.xlAutomatic
            .Order = Excel.XlOrder.xlDownThenOver
            .BlackAndWhite = True
            .PrintErrors = Excel.XlPrintErrors.xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
        End With

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

---

Printing Excel Sheet/Workbook

---

To print an Excel Sheet use this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set the source worksheet
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Printing the Excel Sheet
        xlWorkSheet.PrintOut(From:=1, To:=1, Copies:=1, Collate:=True)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

However when you want to print the complete Excel File then here are two ways you can do it.

If you want to simply print the file without checking the file then you can use this.

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim oProcess As New System.Diagnostics.Process
        With oProcess.StartInfo
            .CreateNoWindow = True
            .WindowStyle = ProcessWindowStyle.Hidden
            .Verb = "print"
            .UseShellExecute = True
            .FileName = "C:\Tutorial\Sample.xlsx"
        End With
        oProcess.Start()
    End Sub

And if you want to print the file from Excel then use this.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Printing the Excel Workbook
        xlWorkBook.PrintOut(From:=1, To:=1, Copies:=1, Collate:=True)

        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
        ' If you just want to print a particular range, say A1:B10 then use the below '
        ' '
        ' xlWorkSheet = xlWorkBook.Sheets("Sheet1") '
        ' xlWorkSheet.Range("A1", "E1").PrintOut(From:=1, To:=1, Copies:=1, Collate:=True) '
        ' '
        '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

---

Protecting/Unprotecting a Worksheet/Workbook

---

If you want to protect the worksheet then use this code

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyPassword As String = "Password"

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        '~~> Protecting the sheet. You have access to many settings which you can set to true or false
        xlWorkSheet.Protect(MyPassword, DrawingObjects:=True, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=False, AllowFormattingColumns:=False, _
        AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows _
        :=False, AllowInsertingHyperlinks:=False, AllowDeletingColumns:=False, _
        AllowDeletingRows:=True, AllowSorting:=False, AllowFiltering:=False, _
        AllowUsingPivotTables:=False)

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

You can protect the workbook in three basic ways

• Structure/Windows Protection
• Open Protection
• Write Protection

Let’s cover them one by one.

Structure/Windows Protection

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyPassword As String = "Password"

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Protecting the workbook structure
        xlWorkBook.Protect(MyPassword, Structure:=True, Windows:=True)
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

Open Protection

Covered in the section “Saving the Excel File (Save / Save As Method)”

Write Protection

Covered in the section “Saving the Excel File (Save / Save As Method)”

---

Saving and Closing the Excel File (Save/Save As Method)

---

There are two way to save the file.

1. Save
2. Save As

When you do a simple “Save” then the original file is saved with the changes that you made. Whereas in the “Save As” a copy of the original file is saved.

See the code snippets below.

Save

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some work

        '~~> Save the file
        xlWorkBook.Save()

        '~~> Close the file
        xlWorkBook.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

Save As

When you do a Save As, you have to specify the new file name and the path for the file. Additionally you can also specify a password to protect the file so that the user has to supply a password to open the file. You can also specify if the file is Write-Protected so that the user has to enter the password before modifying it. If you see the picture below then you can see the various settings that you can have access to)

So a typical code snippet could be like this

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyPassword As String = "Password"

        '~~> Opens Source Workbook. Change path and filename as applicable
        xlWorkBook = xlApp.Workbooks.Open("C:\Tutorial\Sample.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some work

        '~~> Save As file
        xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51, Password:=MyPassword, _
        WriteResPassword:=MyPassword, ReadOnlyRecommended:=True, CreateBackup:=False)

        '~~> Close the file
        xlWorkBook.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

These are the file formats in Excel 2007/2010

50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro’s, xlsb)

51 = xlOpenXMLWorkbook (without macro’s in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2010, xlsm)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

---

Mailing the Excel file using Outlook

---

Before you mail a file from Outlook, click on the menu Project~~>Add Reference. You will be presented with a “Add Reference” dialog box. Navigate to the “COM” Tab and select the Microsoft Outlook Object Library. Once done simply use this code. Amend as applicable.

Imports Excel = Microsoft.Office.Interop.Excel
Imports Outlook = Microsoft.Office.Interop.Outlook

Public Class Form1
    '~~> Define your Outlook Objects
    Dim objOutlook As New Outlook.Application
    Dim objEmail As Outlook.MailItem

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim body As String
        Dim ExcelFile As String = "C:\Tutorial\Sample.xlsx"

        objOutlook = CType(CreateObject("Outlook.Application"), Outlook.Application)
        objEmail = objOutlook.CreateItem(Outlook.OlItemType.olMailItem)

        body = "Hello World," & vbCrLf & vbCrLf
        body += "This is my First email from vb.net. Please find attached the Excel file" & vbCrLf & vbCrLf
        body += "Regards," & vbCrLf & "<Your Name Goes Here>"

        With objEmail
            .Subject = "My First Email"
            .To = "EveryOneInThisWorld@Universe.Com"
            .Body = body
            .Attachments.Add(ExcelFile)

            '~~> Show on screen
            .Display(True)

            '~~> Uncomment the below to acutally mail the file
            '.Send()
        End With
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

This is how you email will look like if you ran the above code

---

Close Excel and Clean Up

---

It is very important to close the objects after you are done with your coding. This ensures that all objects are released properly and nothing remains in the memory.

See this code.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Add a New Workbook
        xlWorkBook = xlApp.Workbooks.Add

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Do some stuff Here

        '~~> Save the file 
        xlWorkBook.SaveAs(Filename:="C:\Tutorial\SampleNew.xlsx", FileFormat:=51)

        '~~> Close the File
        xlWorkBook.Close()

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(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

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub
End Class

---