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.
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.
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:
- They reside in different collections.
- 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.
- It loops through all the custom properties looking for one that matches the propName parameter value.
- 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”.
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.
This sample Excel add-in was developed using Add-in Express for Office and .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