Ty Anderson

Working with Excel workbooks and worksheets: VB.NET examples

We’ve covered how to write code that automates Excel base objects. Today, I want to focus on the Excel workbook file and its worksheets. Let’s take a look at how to perform some useful tasks with these two objects.

Working with Excel workbooks

When you take control of Excel workbooks via code (aka “code against them”), you typically need to know various things about the workbook. Let’s cover the basics by looking at code samples.

ActiveWorkbook object

When the user clicks a button or otherwise initiates a command they almost always want to act upon the active spreadsheet. Excel has an object for this called the ActiveWorkbook and the following function returns it.

Private Function GetActiveWorkbook() As Excel.Workbook
    Return ExcelApp.ActiveWorkbook
End Function

You don’t really need this function. Sometimes, creating examples makes for silly code. But the sample does its job. It shows that a call to ExcelApp.ActiveWorkbook serves up the current spreadsheet for you to do whatever it is you need to do to it.

Get the file path to the active workbook

If you have an ActiveWorkbook, you’re going to want to know things about it. For example its folder path and file name.

Private Function GetFullPath(wb As Excel.Workbook)
    Return wb.FullName
End Function

The FullName properties gives you both.

Get the folder path

Now, what do you do if you don’t want both folder path and file name? What if you want just the folder path? Easy…

Private Function GetPath(wb As Excel.Workbook) As String
    Return wb.Path
End Function

Just grab the Path property.

Get the workbook name

How about if you only want the Excel workbook’s file name? The Name property of a Workbook object provides the spreadsheet’s filename.

Private Function GetName(wb As Excel.Workbook)
    Return wb.Name
End Function

All fair and good.

Save a workbook & get the file format

In baseball the basic skills are throwing and catching. In Excel development, saving files and dealing with different file formats is basic skill. In this sample, the method checks the file format of the passed workbook file and branches based on what it discovers.

Private Sub SaveWB(wb As Excel.Workbook)
    If wb.FileFormat = Excel.XlFileFormat.xlExcel9795 Then
        wb.SaveAs(wb.FullName, Excel.XlFileFormat.xlWorkbookNormal)
    Else
        wb.Save()
    End If
End Sub

If the file is not an XML-based file (i.e. an .XLS file), the code saves it in normal format. Normal is now the Open XML format. Notice how the call makes effective use of the FullName property.

Add a workbook to the Favorites folder

The files utilized by your Excel add-in stand a decent chance of being a user’s favorite…at least for a small period of time. You can make it easy for the user to open these files by creating shortcuts to them in the Windows favorites folder.

Private Sub MakeFavorite(wb As Excel.Workbook)
    wb.AddToFavorites()
End Sub

Protect a workbook

All well-built Excel spreadsheet is worth protecting… especially so if you are automating the spreadsheet in any way. The last thing a developer needs is a user thinking they can edit any-old-cell. They can’t and we don’t need to let them. Here’s how to keep those pesky users in-line:

Private Sub ProtectWB(wb As Excel.Workbook, passWord As String, protect As Boolean)
    If protect Then
        wb.Protect(passWord, True, False)
    Else
        wb.Unprotect(passWord)
    End If
End Sub

Just call Protect or Unprotect and store that password in a safe place.

Create or edit the default document properties

Document properties contain information about the workbook. In the SharePoint world, this information is known as metadata. There are two kinds of properties, default (or built-in) and custom. We have to deal with them separately because:

  1. They reside in different collections.
  2. You can’t create additional built-in properties.

This method edits a built-in property value.

Private Sub EditBuiltInProperty(wb As Excel.Workbook, _
    propName As String, propValue As String)
 
    Dim props As Microsoft.Office.Core.DocumentProperties = _
        wb.BuiltinDocumentProperties
    Dim prop As Microsoft.Office.Core.DocumentProperty = Nothing
 
    prop = props.Item(propName)
    If Not prop Is Nothing Then
        prop.Value = propValue
    End If
 
    'You can choose to save it.
    'I prefer to leave it to the user to do manually
    'wb.Save()
 
    Marshal.ReleaseComObject(prop)
    Marshal.ReleaseComObject(props)
End Sub

The code attempts to retrieve the property using the passed propName value. Because I believe in some good coding practices, the method checks to ensure it has a property before continuing. It avoids errors this way.

Create or edit custom properties of a workbook

For custom properties, I’ve combined the edit and create abilities into a single method. It works like the preceding method except with a couple of exceptions.

  1. It loops through all the custom properties looking for one that matches the propName parameter value.
  2. If the method can’t find the property, it assumes it should exist and proceeds to create it and assign a value to it.
Private Sub EditCustomProperty(wb As Excel.Workbook, _
    propName As String, propValue As String)
 
    Dim props As Microsoft.Office.Core.DocumentProperties = _
        wb.CustomDocumentProperties
    Dim prop As Microsoft.Office.Core.DocumentProperty = Nothing
 
    For i = 1 To props.Count
        If props.Item(i).Name = propName Then
            prop = props.Item(i)
        End I
    Next
 
    If Not prop Is Nothing Then
        'If the property exists, edit it.
        prop.Value = propValue
    Else
        'It doesn't exist...so let's create it.
        props.Add(propName, False, _
            Microsoft.Office.Core.MsoDocProperties.msoPropertyTypeString, propValue)
    End If
 
    'You can choose to save it.
    'I prefer to leave it to the user to do manually
    'wb.Save()
 
    Marshal.ReleaseComObject(prop)
    Marshal.ReleaseComObject(props)
End Sub

In Texas this is what we call a “Twofer“. Twofer is short for “Two for one”.

Working with worksheets

Worksheets are the tabs within an Excel workbook. I tend to think of them as separate spreadsheets but that is probably not the purist view. Anyway, users tend to group data by tab… that is they use different worksheets for different purposes within the workbook file. They are a main character in any Excel custom add-in drama.

Grab the ActiveSheet

Like ActiveWorkbook, the ActiveSheet will typically be the object the user wants to see impacted by any custom action. You can access this object via the ExcelApp object.

Private Function GetActiveSheet() As Excel.Worksheet
    Return ExcelApp.ActiveSheet
End Function

This is convenient. I like that I don’t have to call ExcelApp.ActiveWorkbook.ActiveSheet. That’s too many words and requires too much typing.

Get worksheet name

Each Excel worksheet has a name. If you want to know a sheet’s name, just ask it.

Private Function GetWorksheetName(sheet As Excel.Worksheet)
    Return sheet.Name
End Function

The Name property returns the name on the sheet’s tab. Users can change this name, so be careful using it as it is not always reliable.

Get a worksheet code name

If you want a reliable name, use the CodeName property.

Private Function GetWorksheetCodeName(sheet As Excel.Worksheet) As String
    Return sheet.CodeName
End Function

CodeName does not change when the user edits the sheet name. The only way to change the code name is through the Visual Basic editor’s property window.

Reference a specific sheet

Each worksheet resides in the Worksheets collection. Thus, you can retrieve a sheet by its index value.

Private Function GetSheetByIndex(wb As Excel.Workbook, index As Integer)
    Return wb.Worksheets(index)
End Function

You can also reference a sheet by name by making a call like this one:

Private Function GetSheetByName(wb As Excel.Workbook, sheetName As String)
    Return wb.Worksheets(sheetName)
End Function

Copy or move a worksheet

Just like files, a good worksheet can serve as the template for other worksheets. Invariably, you will need to either copy the worksheet within a workbook or you will need to move a sheet to a new Excel file. This sample handles both tasks.

Private Sub CopyActiveSheet(newWorkbook As Boolean)
    Dim sheet As Excel.Worksheet = ExcelApp.ActiveSheet
 
    If newWorkbook Then
        sheet.Copy() 'Creates a new workbook and copies sheet into it
    Else
        sheet.Copy(, sheet) 'Copies a new sheet after the one copied.
    End If
End Sub

The newWorkbook parameter tells the code which direction to take. The Copy method of the worksheet object completes the task. To make a copy within a workbook, you need to pass a sheet to specify where the copy will reside (before or after). In the sample above, the copy will reside after the original sheet.

Hide a worksheet

Not all worksheets need to be viewed by users. If you need to store some data and then hide it, a worksheet comes in handy. If you do this, you can hide it or you can really hide it.

Private Sub HideSheet(ws As Excel.Worksheet, makeItHard As Boolean)
    If makeItHard Then
        ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
    Else
        ws.Visible = Excel.XlSheetVisibility.xlSheetHidden
    End If
End Sub

The xlSheetHidden value will hide the tab but keep it listed in the workbook’s list of worksheets. The xlSheetVeryHidden value hides the tab and does not list the worksheet. Use this option if you want to prevent a user from un-hiding it against your will.

*****

Admittedly, these samples are just Excel workbook and worksheet blocking and tackling. They’ll get you started. But what is fun is to start developing a solution for actual users. The scenarios they dream-up will take your task to higher level.

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Working With Workbooks add-in for Excel (VB.NET)

Excel add-in development in Visual Studio for beginners:

13 Comments

  • Abb says:

    I am facing a rather peculiar problem in VB.NET related to opening an excel sheet in Windows Forms.

    The data in the excel sheet is simple 5 columns with product, product model, website address url, username, password to access if from the website.
    ALL THIS DATA IS NOT CRITICAL even though it has password in it, also the password field data is simple alpha-numeric.

    I have managed to open an excel sheet in Windows Form using below code

    Dim myConnection As System.Data.OleDb.OleDbConnection
    Dim dataSet As System.Data.DataSet
    Dim myCommand As System.Data.OleDb.OleDbDataAdapter
    Dim path As String = “C:\\Users\\****\\Desktop\\Info.xlsx”

    myConnection = New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + “;Extended Properties=Excel 12.0;”)
    myCommand = New System.Data.OleDb.OleDbDataAdapter(“select * from [Sheet1$]”, myConnection)

    dataSet = New System.Data.DataSet
    myCommand.Fill(dataSet)
    DataGridView1.DataSource = dataSet.Tables(0)
    ‘ alternate row colors
    DataGridView1.RowsDefaultCellStyle.BackColor = Color.DarkBlue
    DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.DarkGray
    myConnection.Close()

    The above code is in a Try/Catch block and as visible in above code I am opening the excel data in DataGridView. When I open this file on desktop using Microsoft Excel I CAN SEE all fields but when I open this in DataGridView some or most of the data from password column is NOT shown.

    I am unable to understand why this is so. Can you pls help me in solving this problem.

    Also I would like to ask should I be using DataGridView or should I use something else to open excel in Windows Form?

  • Ty Anderson says:

    Hi Abb,

    Is it possible for you to share the project?
    I see nothing wrong with using a DataGridView to show the data.

    I did see this article for an ASP app:
    https://www.aspsnippets.com/Articles/Import-data-from-Excel-file-to-Windows-Forms-DataGridView-using-C-and-VBNet.aspx

    Take a look at this line:

    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    Maybe try something similar and use the DataTable as your DataGridView1.DataSource.
    Ty

  • surya says:

    I want to copy data from one excel sheet and want to paste it in to my another sheet in already existing excel in system using vb.net application how to do it?

  • Andrei Smolin (Add-in Express Team) says:

    Hello Surya,

    You can use Macro Recorder to record a VBA macro while performing copying and pasting data in the Excel UI. The VBA macro will show the objects/methods used to perform these steps. Then you will convert that code to the programming language that you use.

  • Daniel Forgacs says:

    Hi Ty,
    How do you dim the ExcelApp? Is it a Global or Public Shared variable, it’s not passed into some of your functions? I was a little lost on how that was done. I appreciate your help understanding!
    Let me know.
    Thanks!

  • Andrei Smolin (Add-in Express Team) says:

    Hello Daniel,

    ExcelApp is a property declared on the add-in module. It returns an Excel.Application; typically, Excel is Microsoft.Office.Interop.Excel. The rest of the code is located in the module and this is how the property is available.

  • A Atique says:

    Hi,

    I have an asp.net (vb.net) application. There is just one new requirement. My user can open a new excel sheet on Browser and type employee ID and Salary in excel sheet. user can put excel function like “=Sum(C1:C10)” in a cell. and then save it. User can make any formating stuff like you do in excel . All functionally should be on browser. Is there any quick way to get this done in Visual Studio.

    Thanks in Advance

  • Andrei Smolin (Add-in Express Team) says:

    Hello Atique,

    There should be a component showing a sheet on an HTML page. I’m sorry, I cannot be more specific as this topic is too far from creating Excel/Office extensions.

  • Leon Lai Kan says:

    Hi,

    I am a newcomer to ADX, and I am learning from the Blog samples.

    At present I am working on this sample:
    Working with Excel workbooks and worksheets: VB.NET examples.

    Here are my steps:

    I unzipped your sample.

    I opened Visual Studio 2010 as administrator.

    I successfully BUILT the project.

    Then I successfully REGISTERED the project.

    Then I START DEBUGGING

    Well, the Excel workbook opens, but I don’t see any trace of the add-in.
    I tried dozens of times (unregistered and registered again), but still no trace of the add-in in Excel.

    What’s more annoying, is that on one occasion, the add-in tab and consorts actually did appear, and I have been able to play with the buttons to see what happens.

    But I could not reproduce the happening.

  • Leon Lai Kan says:

    Hi,

    … Ooops! My comment fired before I finished typing!

    So, what have I done wrong?

    Is this the correct way to run your add-in?

    Thanks
    Leon

  • Tom Mulholland says:

    Hi Team,

    My ADX addin sets OXL as the Excel application, so I can refer to Excel’s sheets collection using a module level variation XL_Sheets:

    Dim XL_Sheets as Sheets = OXL.Sheets

    That works fine and I can loop through the collection.

    However I would prefer also to loop through just Excel’s worksheets collection (as sheets can include charts etc. which I don’t want). I have tried using:

    Dim XL_Worksheets as Worksheets = OXL.Worksheets

    This compiles OK, but when I run, I get a run-time error referring to “System.NullReferenceException, HResult=0x80004003, Message=Object reference not set to an instance of an object.”

    The Worksheets interface doesn’t seem to work in the same way as the Sheets interface.

    Using intellisense when coding, I can see there is an interface to Sheets and Worksheets, am I doing something wrong?

    Thank you for your help !

    Trevor

  • Andrei Smolin (Add-in Express Team) says:

    Hello Tom,

    OXL.Worksheets returns a collection of Worksheet objects in the workbook but the collection itself is of the type Shapes, not Worksheets. Although the Excel Object model contains the Worksheets type, I don’t know how to get an object of that type. Hope, I miss something obvious.

  • Tom Mulholland says:

    Hi Andrei, thanks for checking that out, much appreciated !

    Simplest fudge for now is for me to access the Sheets collection and then make sure the next sheet I want is use something like:

    If [sheet object].Type = xlSheetType.xlWorksheet then …

    so I don’t end up with a runtime error working with a chart or prehistoric xl macro sheet etc.

Post a comment

Have any questions? Ask us right now!