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
• Working with worksheets

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:

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples