The PETRAS application files for this chapter can be found on the CD in the folder \Application\Ch22Using VB.NET and the Visual Studio Tools for Office and includes the following files:
PetrasTemplate.xlt The timesheet template
PetrasAddin.dll The timesheet data-entry support add-in, rewritten as a VSTO assembly and linked to from the PetrasTemplate.xlt template
PetrasReporting.xla The main reporting application
PetrasConsolidation.xlt A template to use for new results workbooks
Petras.mdb A database file to store timesheet data
Debug.ini A dummy file that tells the application to run in debug mode
PetrasIcon.ico An icon file, to use for Excel''s main window
The PETRAS timesheet is an ideal example of the type of workbook for which the Visual Studio Tools for Office was designed. Instead of creating an Excel add-in and distributing it to all our users, the VSTO solution uses a single assembly deployed to a network share and a linked Excel template. The PetrasTemplate.xlt links to the assembly using the custom document properties. To test this example, you will need to copy the PetrasAddin.DLL to a folder of your choice, go through the steps described in Trusting a Strong Name earlier in this chapter and change the template''s custom document property to point to the folder containing the PetrasAddin.dll (using the full folder name). The PetrasTemplate.xlt file should be saved to your Templates folder, usually located at C:\Documents and Settings\<Username>\Application Data\Microsoft\Templates. The PetrasAddin VSTO project contains the following modules:
AssemblyInfo.vb The .NET attributes for this assembly.
MGlobals.vb Global constants and variables.
VSTOHooks.vb Procedures to handle the communication between Excel and the assembly.
CThisWorkbook Class to handle workbook events, most of which were handled at the application level in previous versions of the add-in.
MCommandBars.vb Module to create and remove our command bar buttons. Rewritten to create and destroy them individually instead of using the table-driven command bar builder.
CControlEvents.vb Class to hook the events for our command bar buttons.
MEntryPoints.vb Module containing the procedures called by the command bar buttons.
MBrowseForFolder.vb Module to display a Browse for Folder dialog, rewritten to use the standard .NET dialog.
MDataAccess.vb Module containing the ADO data layer.
MErrorHandler.vb The common error handler routine.
MStandardCode.vb Standard routines, copied between projects.
In this version of the add-in, we''ve moved from an application-centric add-in to a document-centric one, so each timesheet workbook gets its own instance of the VSTO code. The most noticeable change with this is that new timesheets are created using Excel''s normal File > New menu instead of a New Time sheet button on our command bar. The code is unloaded when the timesheet document is closed, so we no longer need an Exit PETRAS button either. The major changes required to convert the PetrasAddin from an Excel add-in to a managed VTSO template workbook are listed in Chapter 22
Module
Procedure
Change
All
All
Converted syntax, object references etc. to VB.NET style.
Multiple
Multiple
Where the Excel add-in code used the ActiveWorkbook in previous chapters, we''re now only interested in the workbook linked to the assembly, given by ThisWorkbook.Workbook.
CAppEventHandler
Removed the class, as each workbook has its own instance of the code, so can handle its own events at workbook level. Moved much of the code to the ThisWorkbook class.
MGlobals
Added global variables from standard ProExcel VSTO template. Changed ThisWorkbook.Path to use the assembly''s path.
MEntryPoints
Removed the procedures for New Timesheet and Exit PETRAS. As this is now an automated timesheet, we''ll use Excel''s File > New to create new ones and the code is automatically unloaded when the timesheet is closed. Removed calls to bInitGlobals as they can now be trusted to remain set.
MBrowseForFolder
Rewritten to use the .NET Browse for Folder dialog.
MCommandBars
Rewritten to create our command bar buttons individually, instead of using the table-driven command bar builder.
CControlEvents (new class)
New class to handle the command bar button Click events.
MErrorHandler
bCentralErrorHandler
Changed ThisWorkbook.Path to use the assembly''s path.
CThisWorkbook
bMakeWorksheetSettings
Rewritten to configure our worksheets individually instead of using a table-driven approach.
PetrasTemplate.xlt
Added setHideRows and setHideCols named ranges to allow the rows and columns to be hidden directly.
Added the _AssemblyLocation0 and _AssemblyName0 custom document properties, to launch the VSTO assembly.
Saved the template in its ready-to-edit state.
The PETRAS reporting application has not been updated for this chapter.