It can be very useful to work directly with Excel in a VB program, either as a source of data or as a destination for it. When given an Excel file as a data source, it would be possible to use Excel to save the file in a csv format for import as a text file, but that adds a step to the process (never welcomed by users). As for using Excel as a destination, that provides an alternative to creating a report; additionally, one that can be manipulated by the user. Conveniently, the coding is similar in both cases.
To work with Excel in VB.Net, first you will need to add a reference to Excel (via the References Page).
The exact library will depend on the version of Excel on the machine. It is also necessary to import that library into any file that will reference it (add Imports Excel = Microsoft.Office.Interop.Excel at the top of the form). Next, you need to declare a variable to use for the application (Public xl As Excel.Application). With these preparations complete, Excel can now be opened for use by the program (xl = New Excel.Application). One danger to be aware of: If there will be a problem accessing Excel, the stored procedure will fail, even when that code is not run. (This will not crash the program, only the active procedure.) This means that a program that is set up to read both Excel and Text files should handle those methods in separate procedures to prevent an Excel issue from blocking the text capability.
At this point, the two paths diverge for a step. If the aim is to import information from a file, you need to open that file (xl.Workbooks.Open(Filename:=<filename>). If the aim is to create a new spreadsheet, you need to open a new workbook (xl.Workbooks.Add()). If the aim is to modify an existing file, you will open the file using the first method, but finish the processing more like a new file. In any case, it may be necessary to change the active worksheet. There are two methods possible – you can select it using the index number (xl.Sheets().Select(1)) or by using the name of the worksheet (xl.Worksheets(“Summary”).Select()). A worksheet can be referenced via a worksheet variable if you chose (Dim A Sheet As New Excel.Worksheet). Since this is a good place for it, I will mention that you are able to change the name for the worksheet should you choose (xl.Sheets(1).name = “Aging”).
From here, for either case, it is a matter of manipulating the data. Fewer tools are needed for reading data from the file, so let’s start there. Basically there are two. The first is used to get the information from a cell in the spreadsheet (xl.Cells(1, 4).Value gives the value in cell D1). You will receive a string, so it may need to be translated before it can be used the way that you want, but it shouldn’t be a major issue. The other useful tool in extracting data is knowing the last row (xl.ActiveCell.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row()). It is possible to use .Column() to reference the last column, but I have never used it myself. When pulling information from a file, it’s really helpful to actually know what data is in which column before you start, so the issue has not come up.
It’s in creating a new spreadsheet where the real fun occurs. It is possible to do anything in code that can be done directly in Excel (at least I have not run into an exception yet). Setting values in the cells is simple, just the reverse of pulling them out (literally – the cell and the variable reverse places in the equation), but that is only the most basic action. Calculated fields are handled slightly differently (xl.Range(“H3”).FormulaR1C1 = “=SUM(R[2]C:R[” & Cntr.ToString().Trim() & “]C)” – this will sum columns in the row, defining their position relative to the target cell).
You can also change the font, its size, or its style. These changes, and any layout changes, can be done for a single cell, a range of cells, or all cells (xl.Cells.Font.Name = “Arial” for all cells, xl.Cells(1, 4).Font.Size = 8 for a single cell, or xl.Range(“A10:E10”).Font.Bold = True for a range of cells). It is also possible to change the alignment for the selection (xl.Range(“F10:G10”).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter) and to create a mask for the field (xl.Range(“H:H”).NumberFormat = “#,##0.00_);[Red](#,##0.00)” would create a number format, = “@” is a text format, and = “mm/dd/yy” is a sample date format).
Resizing rows (xl.Rows(“1:1”).rowheight = 25.5) and columns (xl.Columns(“A:A”).ColumnWidth = 6.43) is a common change. It’s even possible to make the rows resize themselves as needed (xl.Range(“A:J”).EntireColumn.AutoFit() – this would be done AFTER the columns are filled in). You can freeze rows or columns by selecting the row to the rightmost row or bottommost column to be frozen (xl.Rows(“12:12”).Select()). If you want to freeze both rows and columns, select the cell where those rows and columns cross (xl.Cells(1, 1).Select()). Then give the freeze command (xl.ActiveWindow.FreezePanes = True). Merging cells can be accomplished using the merge command (xl.Range(“B2:D5”).Merge).
Once the import or export is completed, Excel needs to be shut down. (Well, technically it could be left up, but that is sloppy coding). In the case of an Export, changes will need to be saved (or what was the point). This is done with a save command (xl.ActiveWorkbook.SaveAs(<filename>) – if this is an update to the existing file, you would use .Save() instead – shocking isn’t it). In either case, the workbook needs to be closed (xl.ActiveWorkbook.Close(), which can accept a parameter SaveChanges:=True to save any changes made or SaveChanges:=False to discard them). At this point, the standard shutdown method will work (xl.Quit()) followed by setting the application variable to nothing.
That is just a sample of the commands that can be used in creating an Excel spreadsheet. It is also possible to change colors, change borders, or shade fields. It appears that anything that can be done by the user in creating an Excel spreadsheet can be done in code (although frequently with more difficulty). Listing every possible option available in the tools would be prohibitively long (and likely not as helpful as might be hoped). The most important trick for duplicating formats (aren’t you glad that you kept reading) is to use the Excel Macro feature. Not, perhaps, in the fashion that you imagine. It is possible to use the Record Macro feature in Excel to learn how to duplicate an action performed manually in code (you will find the feature under macros somewhere – Microsoft loves to move features around, so the Excel version being used matters).
Start the record macro feature (the name used for this purpose is irrelevant). Next perform the action (or actions) that you want to duplicate. Once you finish, stop recording the macro. Next, select the option to view macros and press the button to edit the macro that you just created. This will open a window with the VBA code describing the action just performed. That code will generally not match the VB equivalent, but will at least give an idea of where to look for the needed commands. For some details, this will give exactly the information needed (e.g., what line type was used, exactly what color was selected, etc).
One last little trick: when Excel is used via code, it is possible to control whether the program is visible or not (xl.Application.Visible = True). Running the process with Excel hidden is twice as fast as when the program is visible. But watching the spreadsheet being built automatically is so much fun.
For additional tips and tricks, check out other informative, useful blogs on our site.
Boyer & Associates has been helping Minnesota-based, mid-sized businesses achieve their goals since 1994.
