ISSUE: Personal.xlsb doesn't load

ISSUE:  A user may have setup some personal macros in the Personal.xlsb file but those marcos don't seem to be accessible when opening Excel files from within an Engagement binder.

CAUSE:  Apparently, the Personal.xlsb file doesn't load when Excel is started with automation (think opening Excel files within Engagement).

RESOLUTION:  Navigate to the location of the Personal.xlsb file and open it.  You may need to unhide it before continuing.  Do a "Save As" and save the file as an Excel Addin (".xla") which should save the file in to the appropriate directory on the user's machine (which should be in the "C:\Users\[username]\AppData\Roaming\Microsoft\Addins\" directory for Windows 7 users).  Once the Addin file is saved and Excel has been closed, move the Personal.xlsb file to a different location (just to make sure it doesn't get loaded) and reopen Excel.  If the Addin file isn't loaded, make sure the Addin file is set to be loaded (meaning "checked") in the Excel Addins dialog (Excel button | Excel Options | Addins | Excel Addins).

Any Addin file that is set to be loaded should be available when opening files from within Engagement.

Issue: Large Excel Files

We have a client that provides large Excel files (.xls) that contain 60,000 to 100,000 rows of information.  That equated to a 38MB file.  We wanted to reduce the size of the file for our binder so we saved it as an Excel binary workbook (.xlsb) and then inserted it into the binder.  To our surprise, when we tried to open the file from within Engagement it didn't open.  Tried opening it on different computers and still no luck.

I called Engagement Support and was told that Excel binary workbooks were not listed as compatible file types but that "if it can be opened outside Engagement you should be able to open it from Engagement."  I was also told that no one on the product team had tested compatibility with Excel binary workbooks.

So, in short, no luck with binary workbooks for now.  If it's any consolation, a request for enhancement was filed.

Issue: Custom Functionality in Workpapers

Issue: In some cases, users will need (or should have) custom functionality within Word, Excel, or PDF documents to enhance their workflow or reduce required time on the workpaper.

Resolution: Just because documents are stored in Engagement doesn't mean that users can't have custom functionality in the Office suite applications. One great thing about Word and Excel is that Microsoft created them to be open enough for developers to create functionality that does not exist out of the box. (On a side note, some open source office suites provide similar custom functionality options.) Acrobat also has custom functionality options similar to Microsoft products but I will stick to Word and Excel.

There are two main ways to create custom functionality in Word or Excel (or any Microsoft Office application): (1) Macros stored in a file or (2) a custom add-in. A short discussion of each as they relate to Office 2003 is below.

Macros stored in a file

Many users have done this and may not know it. If you have recorded a macro and stored it in the Normal.dot (Word) or Personal.xls (Excel) you have done a simple form of this. Macros can be recorded or written from scratch depending on their complexity. The more complex a macro is, the more "writing" from scratch you'll have to do. Once the macro is finished, it will need to be stored in a special type of file; a template (.dot) for Word and an add-in file (.xla) for Excel. Both types of files are accessible from the "Save as..." option in the File menu from the applicable application. These file should then be saved in the appropriate folders on each users computer so the application knows they're safe to load at startup. A detailed example will be posted later.

A custom add-in

Custom add-ins are developed using an integrated development environment or IDE. Software developers and other advanced programmers use IDEs. Microsoft's Visual Studio is an example. IDEs offer functionality that macros don't enlarging the possibilities of the custom functionality but also may become more complex fairly quickly so be careful. A detailed example will be posted later. Distribution of a custom add-in is fairly easy once some setup is complete.

NOTE: Just about any custom functionality developed for Office 2003 will still work in Office 2007.

How To: Remove all tickmarks in an Excel document at the same time

Issue: Many users have asked if there is a way to remove all the tickmarks from an Excel document at the same time. This especially applies when a binder is rolled forward and the workpaper is marked as "Rollforward." When a workpaper is rolled forward, all the tickmarks stay the same.

Resolution: There is native functionality in Excel (and Word for that matter) that allows a user to remove any or all of the shapes in the document at the same time. Excel considers tickmarks to be shapes so this options works well. One button will have to be added to a toolbar in Excel to get this option. The steps for adding the toolbar button and actually deleting the tickmarks are below.

Add Toolbar Button

1. 1. In Excel, go to View Toolbars Customize or right-click on the any toolbar and select Customize at the bottom of the pop-up menu
   
2. 2. Click on the Command tab
   
3. 3. In the left panel, select Drawing
   
4. 4. In the right panel, click and drag Select Multiple Objects to an existing toolbar
   
5. 5. Click Close

Removing Tickmarks

1. 1. Click the Select Multiple Objects button just added to the toolbar
   
2. 2. Select objects to be removed (see NOTE below)
   
3. 3. Click OK
   
4. 4. Press the Delete key on the keyboard

NOTE: As a practicle matter, some workpapers can have a large number of tickmarks on them. In those cases, it may make more sense to press Select All in step 2. of Removing Tickmarks instead of selecting each tickmark individually. If this is done, be sure to uncheck each shape that is not a tickmark. The names of the shapes will help in differentiating between tickmarks and other shapes. Tickmarks are added as pictures and other shapes, such as textboxes and arrows, are added using different names.