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 Smile

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.

image

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.

image

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.

image

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 Smile

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.

image


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

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.

image


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

image

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.

image


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.

image

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

image

This code will help us achieve it Smile

    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.

image

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

image

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 Smile

image
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 Smile

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.

image


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.

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.

image

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


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

image

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.

image

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)

image

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

image


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

71 responses to “VB.NET and Excel

  1. Russell October 18, 2011 at 1:59 pm

    Hello siddharth :D
    sorry for being such a bad learner but..
    how do i write the formula that corresponds to the block before it?
    i know i can write something like
    .Range(“A2″).Formula = “=A1/1000000″
    but is there a way so i can make a For loop so i dont have to type this formula a lot of times?
    Sincerely,
    Russell(Raskyl)

    • Russell October 18, 2011 at 2:01 pm

      er i mean.. i have values in A2 all the way to A19
      So my formula was supposed to be .Range(“B2″).Formula = “=A2/1000000″
      but yeah. question will be the same :)

    • Siddharth Rout October 18, 2011 at 2:08 pm

      Hello Russel

      Please find 2 ways (There are more) on how to achieve what you want :)

      ‘~~> Note: oSheet is youe Sheet object which you have defined.
      ‘~~> Two ways mentioned below.

      ‘~~> WAY 1: By Looping
      For i = 2 To 19
      ‘~~> For Example B2 has =A2/1000000
      oSheet.Range(“B” & i).Formula = “=A” & i & “/1000000″
      Next i

      ‘~~> OR

      ‘~~> WAY 2: Using AutoFill method
      With oSheet.Range(“B2″)
      .Formula = “=A2/1000000″
      .AutoFill Destination:=.Range(“B2:B19″)
      End With

      Hope this helps? :)

      • Russell October 18, 2011 at 2:30 pm

        Hi Saddharth,
        that looks good :D i’m going to test it out now ! thanks so much :)

      • Russell October 18, 2011 at 3:34 pm

        Hello again Siddharth!

        it works like a charm! thank you so much :D
        now i just wanna know if i can replace the Row A2 onwards with the data from B XD i feel like i’m asking a lot haha =X sorry but still, Thank you a bunch!

      • Siddharth Rout October 18, 2011 at 4:24 pm

        Not sure what exactly do you mean? Can you give me an example of what you want?

      • Russell October 19, 2011 at 7:39 am

        Hi Siddharth,
        i thought i could replace my Row A with the values i got from Row B. but i could not .
        because the values i got in Row B is what i need, not Row A.

      • Siddharth Rout October 19, 2011 at 12:43 pm

        So is your problem solved? Else you can do a paste special values on Col A after you copy from Col B…

      • Russell October 19, 2011 at 12:56 pm

        the code you give me works very well! :)
        i just wonder if it can just be made so that the value in B would be written into A
        so all the A results will be 2000 instead of the original 2000000000 and B will be erased so i would not have 2 of the same values in the excel sheet.

      • Siddharth Rout October 19, 2011 at 1:10 pm

        Yes like i said you can copy col B and do a PasteSpecial Values on Col A and then delete Col B :)

        Use this code after you have populated Col B with the above code. ;-)

        With oSheet
        .Columns(“B:B”).Copy()
        .Columns(“A:A”).PasteSpecial(Paste:=-4163, Operation:=-4142, SkipBlanks _
        :=False, Transpose:=False)
        .Columns(“B:B”).Delete(Shift:=4159)
        End With

      • Russell October 19, 2011 at 1:20 pm

        oh my god! thank you so much! That is really exactly what i need. thank you so much! :D

  2. Jan October 19, 2011 at 12:05 pm

    Hi Siddharth,

    Can you please help me with the below post..

    http://social.msdn.microsoft.com/Forums/en-NZ/exceldev/thread/d7571669-3ea5-48de-a3dc-00cb9863c121

    I think the problem is with the tool and not with the code
    Jan

  3. Rakesh October 19, 2011 at 4:10 pm

    awesome code …really superb

  4. Bob February 6, 2012 at 1:32 pm

    Hi there !

    Code is awesome ! I got some problems, cause I use MS 2003 this is not work for me:

    ‘~~> Inserting a Graph
    .Shapes.AddChart.Select()
    xlApp.ActiveChart.ApplyCustomType(Excel.XlChartType.xlLineMarkers)
    xlApp.ActiveChart.SetSourceData(Source:=.Range(“Sheet1!$A$1:$B$5″))

    In first line i got an exception:
    Public member ‘Shapes’ on type ‘Range’ not found.

    My secon question is how to for example loop trought some column from up to last filled cell and sum all of values without null cells.

    best regards,
    Bob

  5. Bob February 6, 2012 at 3:44 pm

    Another problem is i can’t select a columns I try the code which works in excel vb but in vs i receive an error:
    xlWorkSheet.Range(xlWorkSheet.Columns(LastFilledColumnDest), xlWorkSheet.Columns(LastFilledColumnDest)).Select()
    ‘LastFilledColumnsDest is number of column

    Error is:
    Select method of Range class failed

    I tried this and and an error too:
    ‘Dim rg As Excel.Range = xlWorkSheet.Columns(9)
    ‘rg.Select()

    Please of help

    • Siddharth Rout February 7, 2012 at 4:26 am

      Is there any specific reason why you want to select the columns. We should avoid using .Select() as much as possible. Remember that you can perform actions on Columns without selecting it.

  6. Ryan February 7, 2012 at 4:15 am

    Great stuff!! Keep up the good work!!!

  7. Loyd March 10, 2012 at 10:05 am

    Thank you for a most informative and helpful post. I had figured some of this out, but couldn’t quite get everything I needed.
    L…

  8. Dayanand March 11, 2012 at 2:56 pm

    nice blog i like it can you guide me in other some things

  9. Ngurah Agus Sanjaya Erawan March 23, 2012 at 9:23 am

    wow this article covers everything I need to know about vb.net and excel. I really love it and thank you very much for this helpful guide. just one question: do you have it in pdf format? because I want to read it offline instead of going back online every time I forget something. if you do please send it to my email address. thanks and keep posting.

  10. Ngurah Agus Sanjaya Erawan March 23, 2012 at 6:52 pm

    Dear Siddarth,

    I need your help here please. I am creating an excel sheet and fill it with data from mysql table. Because the data is dynamic (number of rows is not the same all the time), I need to put the appropriate data in the exact place on the excel sheet. I need to be able to access a certain cell according to a counter. I tried to do this:

    intCnt = 26
    strTmp = “B” & intCnt.ToString
    .Range(strTmp).Value = reader2.Item(“uraian”)

    That code put nothing in the cell. Can you please tell me how to overcome this problem? Thanks in advance.

  11. Mahendra P. Verma March 27, 2012 at 12:23 am

    Dear Siddharth,

    Nice job for working in Excel from VB.NET.

    Please can you advice how to use a control in Excel, which is developed in VB.NET. For example, I created a control from steam tables (i.e. thermodynamic properties of water) as SteamTablesGrid (published in Computers & geosciences, 2011). I wish to use it in Excel.

  12. Benzadeus April 11, 2012 at 8:32 am

    Hello,
    I am the owner of site http://www.ambienteoffice.com.br/
    I really liked this page, and would like to translate it to upload to my website, giving you the credits. Is it okay?
    Keep the good work.

  13. SJRinOmaha April 24, 2012 at 1:26 am

    Tremendous information!! Thank you for taking the time to put this comprehensive page together – it helped me greatly!!

  14. Murli April 25, 2012 at 3:24 pm

    Nice Compilation …Can you present the following in C#

  15. Sanjay G April 26, 2012 at 9:44 am

    Simply awesome, keep the site always live, it’ll help millions.

  16. Isha Maurya May 14, 2012 at 3:45 pm

    hi Siddharth,
    i am exporting my grid and chart to excel,but my chart has legend name series1 ans series 2…i want it to change to my columns name.Can u please tell how to change the legend name.

  17. Nitin June 29, 2012 at 5:02 pm

    Hi Sidhhartha,
    I am using the following code in excel macro to convert from text to column..

    ‘ For Power values From text to column
    Range(“R6:R30″).Select
    Selection.TextToColumns Destination:=Range(“BK6″), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
    Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    I am trying to repeat the same using VB2010. what will be VB2010 equivalent of it? . My only problem is I have data in the form of “XC6 -2.50″ and I want to extract only the number part “-2.50″ for which I am using text to column property. Can you please help?
    thanks
    nitin

    • Siddharth Rout June 29, 2012 at 5:13 pm

      Nitin, Please see my other links on how to work with Excel Ranges from VB.Net. Regarding the Excel Constants like xlDelimited, xlDoubleQuote, you have to fully qualify them for example

      DataType:=Excel.XlTextParsingType.xlDelimited

      Either you can do that or you can directly replace it with it’s value for example

      DataType:= 1

      You can get the constant values from the Immediate window in Excel VBA by typing

      ?xlDelimited

      • Nitin July 4, 2012 at 1:20 pm

        Hi Sid, Many-2- thanks for the post and the solution. Sorry for the reply as I was busy trying several things around this. Finally I could get what I wanted. thanks again for the help.
        On a separate note, it will be nice if you can also post on how to use dos command prompt (shell) using VB.net, how do you run several commands on dos prompt using VB.net. One of the items I am working on, say one program is running on DOS, how do I know when this is completed? As the completion of one should trigger the execution of next task, how to add time delay in the program, how to ping several servers to check if a particular program is running on that server. these are some of the ideas which could be of interest to many…
        thanks again… you rock..:)

  18. Dave July 2, 2012 at 5:41 pm

    Hi Siddarth – do you have any information on how I can take a control text from a combobox in a VB form and copy it to an excel sheet combobox activeX control? I can get the excel worksheet open etc – I simply cannot get the chosen value from the form to the excel control

  19. Dave July 3, 2012 at 2:38 am

    I am using Visual Studio 2010. I have created a form with combobox/textbox’s on it. User chooses the value or types it in. I then need to be able to export the values to an excel spreadsheet with corresponding combobox/text box etc – I need both as the VB application has some other uses but at times the users require a spreadsheet version which is easily printed etc.
    So combobox1 has a value in VB.net of Yes and I want the spreadsheet combobox1 version to be updated to Yes as its value.

    I can do this using a VBA form to word but not using the VB fom to excel. Any suggestions will be appreciated

  20. Alphin Thomas July 6, 2012 at 3:43 pm

    First of all Siddharth I would like to thank you for taking out the efforts to create such a helpful post for everyone.
    I would like your help for one small topic. I am using VS 2010 Express and MS Excel 2007 wherein I am trying to export data from the DataGridView into an Excel file.

    xlWorkSheet.SaveAs(“C:\ExportTagDetails.xlsx”)

    the above line of code works for me. But if I again try to create a file, it obviously shows the Message : ‘A file named ‘C:\ExportTagDetails.xlsx’ already exists in this location. Do you want to replace it?’
    When I press Yes it creates no problem and calmly replaces itself in place of the old file. But if I click on No or Cancel then it throws an exception stating : ‘System.Runtime.Interop.Services.COMException(0x800A03EC): Exception from HRESULT: 0x800A03EC’

    Here is the entire block of code :
    Private Sub cmd_export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_export.Click

    Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer
    Dim row As Long = 1

    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets(“Sheet1″)
    xlWorkSheet.Cells(1, 1).Font.Bold = True
    xlWorkSheet.Cells(1, 1).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
    xlWorkSheet.Cells(1, 2).Font.Bold = True
    xlWorkSheet.Cells(1, 2).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
    xlWorkSheet.Columns(1).ColumnWidth = 45
    xlWorkSheet.Columns(2).ColumnWidth = 30
    xlWorkSheet.Range(“A” & row).Value = “Tag Name”
    xlWorkSheet.Range(“B” & row).Value = “Start Value”
    row = row + 1
    Try
    For i = 0 To DataGridView1.RowCount – 2
    For j = 0 To DataGridView1.ColumnCount – 1
    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
    Next
    Next

    xlWorkSheet.SaveAs(“C:\ExportTagDetails.xlsx”)
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try
    xlWorkBook.Close()
    xlApp.Quit()

    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)

    MsgBox(“You can find the file at C:\ExportTagDetails.xlsx”)
    End Sub

    Is there any way I can handle the issue.
    Or can you provide me with a solution where the user can himself enter the name of the file and also specify the location.
    Thanks in advance.

    • Siddharth Rout July 12, 2012 at 6:35 pm

      Alphin, You don’t need a Try Catch here. You can simply use “If Not IO.File.Exists(“C:\ExportTagDetails.xlsx”) Then” to check for the existence of the file and then do a save as so that the question of OK/Cancel doesn’t arise in the first place :)

      • Alphin Thomas July 13, 2012 at 11:11 am

        Thank you for the reply. But i figured out a way by using the SaveFileDialog component.
        Now I am able to save the excel file at the location I want and name it accordingly.
        Cheers :D

  21. Sreejith July 12, 2012 at 11:32 am

    That was cool…….Thank u for the codes……….

  22. Nitin July 17, 2012 at 1:00 pm

    Hi Sid, I created a program in VB.net say xyz.exe. This program requires an excel input file which i am currently opening using a form. Program does some processing, copy data from input excel file to another file and run some commands on that. Now my requirement is that I dont want to use forms and make it command line based. The input file should be passed as an argument on command line without opening any form. So finally it should look like “c:\xyz.exe input.xls RedBand”. How can I modiffy the existing program to be used from CLI. Please help.
    thanks
    Nitin

  23. Eruditio July 18, 2012 at 7:46 pm

    This is an incredibly useful page. Thank you for creating this! I looked everywhere to find some useful information about editing the print settings for Excel from within VB.NET.

    I do have one question however since it doesn’t seem obvious from the information provided. I would very much appreciate a response but understand that you might be too busy.

    You list a number of properties to change the Page Setup and a few ways to print an Excel document from VB. Is there a way to set the Excel workbook print properties to ‘Print entire workbook’, without actually printing the document?

    • Siddharth Rout July 18, 2012 at 8:42 pm

      Thanks :) Well, It doesn’t appear that there is a registry entry for this. This again might not be a good idea. Why would you want to force this “setting”? What if I wanted to copy only 1 sheet? If this is for an Add-In/Workbook that you made then you can handle it via code for that particular Add-In/Workook which will force printing of all worksheets all the time.

      • Eruditio July 18, 2012 at 9:11 pm

        Thank you for the swift reply! I didn’t expect to hear back so soon.

        I have developed an application that allows clients to enter a document name and view the corresponding Excel spreadsheet stored at a particular location on a server. The intention is to make the interface as simple as possible because those using the application might not have a tight grasp on computing, even something like changing the print settings.

        In the majority of cases, these users would like to print the entire workbook for the Excel spreadsheet, and it would be quicker and easier (and cheaper, in terms of training) for them to hit the ‘Quick Print’ button in Excel Viewer (the computers they are using have limited capabilities – Excel Viewer instead of Excel; it’s free and has all the capability they require) rather than navigate via the Microsoft Office Button –> Print –> Print and then change the settings.

        It would be possible, using the code snippets you’ve provided, to place a print button in the VB form. This would require the user to minimise the Excel document to print, however, which is non-intuitive. There are also many problems in the instance that they have multiple Excel documents open. I would rather not have to restrict their ability to open multiple documents simultaneously.

      • Siddharth Rout July 18, 2012 at 9:25 pm

        Have you considered embedding the document in your application and hiding the Ribbon? You can then create a customized button to do the printing? You might want to see this link? http://siddharthrout.wordpress.com/2012/07/02/embed-excel-documents-in-vb-net-application/

      • Eruditio July 19, 2012 at 3:17 pm

        I’ve looked at that page before and decided that I don’t want to have to pay for that component.

        What I’ve ended up trying to do is to use a Microsoft Web Browser control (added by right-clicking the Toolbox) to display the Excel document. I’ve figured this out (after playing with the registry), and can now get the Excel document to display within the form. There is no Excel ribbon by default, it seems, and the document opens as read-only, all of which is good. I’ve added a print button, and this is where I’m stuck again. I don’t seem to be able to figure out how to print the entire workbook. At the moment I’m using the ExecWB method of the AxWebBrowser control:

        AxWebBrowser1.ExecWB(SHDocVw.OLECMDID.OLECMDID_PRINT, SHDocVw.OLECMDEXECOPT.OLECMDEXECOPT_DONTPROMPTUSER)

        This prints the active sheet without a prompt to the user. I feel like I’m close, but this last part is eluding me!

  24. Nitin July 19, 2012 at 3:06 pm

    Hi Sid, I need your help in small problem in using VB.net. I have an excel file with lots of data. I want to first search for a heading say “Node Type”, once I find this cell (containing a particular heading) say X3, I want to copy X4:X20 in some other file. Can you please help. thanks..

    • Siddharth Rout July 23, 2012 at 12:07 am

      I would recommend posting the question in a forum like vbforums.com/msdn/stackoverflow and then send me a link. I will reply to you there :) BTW, you would be using .FIND() to find the text in excel ;)

  25. Bhupen July 24, 2012 at 11:54 pm

    Hi Siddhart, Am tryin to save this excel sheet oSheet.SaveAs(C:\Users\Admin\Desktop\business.xls) however i was wondering if i could add current date to this file number eg. if my app is generatis file today, file name should have today’s date.

  26. Tata December 12, 2012 at 2:57 pm

    Very good article! Salute!

  27. Marvin Rodriguez January 8, 2013 at 1:56 am

    Man, thanks you are so pro!. Thanks for give us code!.

  28. kamal January 17, 2013 at 2:44 pm

    Amazing…. keep post

  29. Deepak May 20, 2013 at 12:40 pm

    Excellent article
    thanks

  30. Rangga Stephen Jampi June 8, 2013 at 6:36 pm

    Thank you sir..this is what i’m looking for. nice post

  31. saliza daud August 1, 2013 at 2:14 pm

    U HELP ME A LOTS…TQ SOOOOOO MUCH

  32. Andy Cheng August 21, 2013 at 11:52 am

    a very useful introduction. thanks for sharing.

  33. Ron August 31, 2013 at 1:53 am

    Siddharth,

    Thank you so much for taking the time to make such a useful training tool!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: