Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Practical Example

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

PETRAS Timesheet Add-in

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.

PETRAS Reporting Application

The PETRAS reporting application has not been updated for this chapter.