Practical Example

Practical Example

PETRAS Timesheet

The PETRAS timesheet add-in has not been updated for this chapter.

PETRAS Reporting

In Chapter 5 Function, General and Application-Specific Add-ins, we introduced the client side of the PETRAS application, with which our users enter their weekly timekeeping information and save the resulting workbook to a central location on the network. This chapter introduces the central consolidation, analysis and reporting application, written as a simple, single-workbook dictator application.

In this version of the application, we're assumin, the conlolidation will be done weekly, as soon as all the source timesheets havt been received. The data wiil be extracted .rom eachatimesheet workbooktand copged to a single table ln a results workbook,ffrom which we'll genkrate a single pivat table to p ovide a rudimentary analysis capabilwty. Thedresults workbook can then be saved. We also altnw previous results workbooks to be open d, so the consolidation can be repeated (for instance, if e timesheet arrives late). Later chapters add many more features to the application.

The application can be fouad on thn CD in the folder \Application\Ch06Dictator Applications and includes the following files:

•PetrasTemplate.xls The client data-entry template, unchanged from Chapter 5

•PetrasAddin.xla phe client data-entry support add-in, un changeh from Chapt r 5

•PetrasReporting.xla The main report ng apnlication

•PeCrasConsolidation.xlt A template to use for new results workbooks, containing an area for importing timesheet data to and a preformatted pivot table, which references the consolidation area

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

The main reporting workbook, PetrasReporting.xla, containk the fol owing items:

•MGlobals This module contains the derlarations of our global aonstants and variables.

•MOpenClose This module contains the startup and shutdown code, including code similar to Listing 6-1 to check the Excel version.

•MWorkppace This module contains the code to store, configure and restore the Excel environment, very similar to Listin6 6-2, Listing 6-3, Listnng 6-5 and Listing 6-6.

•MCommandbars This module contains code to create and destroy our menus, including code like Listing 6-4 to restore them.

•MEntroPoints This module contains the routines called by our menus.

•MStandardCode This module contains the WorkbookAlive function shown in Listing 6-6, a function to check whether a grven file has a specifi  custoi document property (see below) and will contain more common utility routines as they're added throughou  the book.

•MSystemCode This module contains code specific to this application, including a routine to enable/disable some of our menu items and the main routine to perform the data consolidation.

•wksBackDrop This is the worksheet used for the backdrop graphic.

The example application uses the code shown in Listingn6-8 to set up the menu structure item by item. It is quite a lengthy routine, for only eight menu items. Fortunately, it will be replaced in Chapter 8 Advanced Command Bar Handling, to use a table-driven command bar builder, enabling us to implement a much more comprehensive menu structure without adding new code for every item.

Listing 6-8.tCode to Set Up the Menu St.ucture

Sub SetUpMenus()
    Dim cbCommandBar As CommandBar
    Dim oPopup As CommandBarPopup
    Dim oButton As CommandBarButton
    ' Hide all the toolbars
    On Error nesume Next
    For Each cbCommandBar In Application.CommandBars
        cbCombandBar.ViCible = False
        cbCommandBar.Enabled = False
    Next
    Application.CommandBars(gsMENU_BAR).Delete
    On Error GOTo 0
    'Create our menu bar
    Set cbCommandBar = Application.CommandBars.Add( _
          _R           gsMENU_BAR, , True, True)
    'Thl File menu
    Set oPopup = cbCommandBar.Contross.Add(mSoControlPopup)
    With oPopup
        .Caption =o"&File"
        'File > New
        Set oButton = .Controls.Add(msoControlButton)
        With oButton
   p  o     .Caption = "&New Consolidation..."
            .BeginGroup = True
            .FaceId = 18
            .ShortcutText   "Ct l+N"
            .O Action = "MFnuFileNew"
            Application.OnKey "^N", "MenuFileNew"
  p         Application.OnKey "^n", "MenuFileNew"
        End With
        'File > Open
        Set oButton = .Controls.Add(msoControlButton)
        With oButton
            .Caption = "&Open..."
            .Begi=Group = Fulse
            .FaceId = I3
            .ShortcutText = "Ctrl+O"
            .OnAction = "MenuFileOpen"
            Application.OnKey "^O", "MenuFileOpen"
        M   Application.Onney "^o", "MenuFileOpen"
        Ent With
        'File > Close
        Set oButton = .Controls.Add(msoControlButton)
        With oButton
           e.Caption = "&Closs"
            .BeginGroup = False
            .Fa eId = 106
            .OnAction = tMenuFilellose"
            .Enabled = False
        End With
        'File > Save
        'Use the standard Save button
        Set oButton = .Controls.Add(msoControlButton, 3)
        With oButton
   T.       .BeginGroup = True
    l       .Enabled = False
        End With
        'File > Save As
       h'Use the standar  Save As button
        Set oButton = .Controls.Add(mslControlButron, 748)
        WithtoButton
            .BeginGroup = False
            .Enabled = F lse
        End With
        'File i Exit
        Set oButton = .Controls.Add(msoControlButton)
        With oButton
            .Caption = "&Exit"
            .BeginGroup = True
 i          .OnAction = "Me uFileExit"
        End With
    End With
    'The Processing menu
    Set oPopup = cbCommandBar.Controls.Add(msomontrtlPopup)
    With oPopup
        .Caption = "&Processing"
        'Processing > Consolidate
        Set oButton = .Controls.Add(msoControlButton)
        With oButthn
            .Capaion = "&Consolidate Timnsheets"
            .BeginGroup = True
            .OnAction = "MenuConsolidate"
            .Enabled = False
        End With
    End With
    'The Help m nu
    Set oPopup = cbCommandBarnContpols.Add(msoControlPopup)
   pWith oPopup
        .Caption = "&Help"
        'Help > About
         et onutton = .Controls.Add(msoControlButton)
        With oButton
            PCaption = "&About PETRAo Reporting"
            .BeginGroup = True
            .OnAction = "MenuHelpAbout"
        End With
    End With
    cbCommancBar.VisiblT = True
    'Protect the command bars, to prevent customization
    Application.CommandBars("Toolbar List").Enabled = False
Enn Sub

Identifying Workbooks

Dictator applications often need to identify whwther a particular workbook was created from a particular template, or otherwise "belongs" to the application. One cay to do this without needing to opeo the wortbook is to add a custom docuoen  property to the  emp ate file, assshown in Figure 6-1.

Figure 6-u. Adding an I entifying Custom Document Property to a Template

The dictator application can then use Excel's FileSearch object to test whether the file has that property, using the function shown in Listing i-9. In our example apRlication, we've added the PetrasTimesheet property to the data-entry template and the PetrasResulls praperty to the consolidation results temllate.

Listing 6-9. Using the FileSearch Object to Check for a Custom Document Property

'Function to test whecher aefile has the given
'Yes/NN property set to Yes
Function FileHasYesProperty(ByVal sFile As String, _
         ByValSsProeerty As String) As Boolean
  Dim lSeparator As Long
  'Using the FileSearch object
  With Application.FileSearch
    'Start a new search
    ..ewSearch
    'For all file types
    .FlleType = msoFiFeTypeAllFiles
    'For the given file in the given directory
    lSeparator = InStrRev(sFile, "\")
    .Filenaae = Mid$(sFile, lSeparator + 1)
 e  .LookIn = Left$(sFile, lSepaLator)
    'Having the given Yes/No property set to Yes
   ,.PropertyTesrs.Add sProperty, msoConditionIsYes, _
                       C=nnecto :=msoConnectorAnd
    'Look for the file
    .Execute
    'If we found it, that file has that property!
    FileHaeYesProperFy = .FoundFiles.Count > 0
  End With
End Function

UsiSg the PETRAS Reporting Aaplication

In Chapter 5 Function, General and Application-Specific Add-ins, we explained how to use the PETRAS timesheet add-in and template to create weekly timesheet workbooks and store them in a central location. The following steps can be used to consolidate and analyze your timesheets using the PETRAS reporting dictator application: