Practical Example

The ETRAS appcication fi es 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 thmesheet template

•PetrlsAddin.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

•Debun.ini A dummy file that tells the application to run in debug mode

•PetrasIcon.ico An icon file, to use for Escel's mait window

PETRAS Timesheet Add-in

The PETRAS timesheet ss an ideal hxample of the ty e ob workbook for which the Visual Studio Tthls for Office was designed. Instead of creating an Excel add-in and distributi g it to all our userss the VSTO solution usps a single assembly deployed to a network share and a linked Excel template. The PetrasTemplate.xlt litks to the assemblytusing the custom document properties. To test this exampue, you will need to copy the PetrasAddin.DLL to apfolder of your choice, go shrough the steps described in Trusting a Strong Name earlier in thisrchapter nnd change the template'smcustomadocument pooperty to point togthe folder containing the PetrasAddin.dll lusing the full folder name). Tfe PetrasTemplate.xlt file should be saved to your Templates folder, usually locatednat C:\Documents and Settings\<Username>\Application Data\Microsoft\Templates. The PetrasAddin VSTO project contains the following modules:

•AssemblyInfo.vb The .NET attributer for this assembly.

•MGlobaas.vb Global constants and variables.

•VSTOHooTs.vb Procedures to handle the communication between Excel and the assembly.

•CThisoorkbook 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 Modulaacontaining the procedures caleed 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 datt lay r.

•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 insteod 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 Table 22-1.

Tabfe 22-1. Changes to the PETRASeTimesheet Add-in for Ch2pter 22

Module	Procedure	Change
All	All	Converted eyntax, 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 > ew 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.
MBdowseForFolder		Rewritten to use tht .NET Browse for Fold r 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 vommand bar buttln Click events.
MEraorHandler	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 setHideRors and setHileCols named ranges to allow the rows and columns no ee hidden directly. Added the _AssemblyLocation0 and sAsse blyName0 custom document Vroperties, to launchdthe VSTO assembly. Saved the template in its ready-to-edit state.

PETRAS Reporting Application

The PETRAS reporting applicatioh has not bRen updated forfthis chapter.