I have a financial model that I set up using a grouping in some key places so that I could collapse sections of the model when I didn’t want to look at them. As I was handing off the model to someone else to work with, I wanted to protect the worksheets, but unfortunately there is no setting in the user interface to allow for expanding/collapsing the outlining tools when the sheet is protected. In fact, trying to do so gives you the following message:

I found this a little frustrating, but gave up on it. I expanded the model completely, protected the sheets and let the users have at ‘er.

Tonight at VBAExpress.com though, I was posting on a thread where the user had included the following in their code:

Sh.EnableOutlining = True

Wow! So obviously there IS a way to enable the outlining tools when the worksheet is protected, right? I ran the macro I had modified for the user and sure enough it worked. Cool!

So then I opened up a copy of my model and:

• Ran the following code: Activesheet.EnableOutlining = True
• Protected the worksheet

I didn’t work. What the hell?

After a little sleuthing I found out what the issue was. In order for the EnableOutlining to take effect, you must run the code that protects your worksheet with the userinterfaceonly:=true argument.

The unfortunate part of this is that userinterfaceonly:=true doesn’t stick between sessions. So that nice macro free workbook is now going to have to be saved into an xlsm format with the following code in it:

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

With ws

.Protect userinterfaceonly:=True

.EnableOutlining = True

End With

Next ws

Application.ScreenUpdating = True

End Sub

That shouldn’t be necessary in my opinion, but whatever. A macro laden file is a small price to pay for the functionality. Man I love VBA!

Today I realized that one of my GETPIVOTDATA formulas was returning an error, since there wasn’t any result in the Pivot Table it was checking for data. Unfortunately, during the design of the spreadsheet I never wrapped the function with IFERROR to avoid this.

The challenge I had was that I had several different formulas that needed to be wrapped, and didn’t want to spend the time adjusting each one manually. So I wrote a little macro to adjust the existing formulas. Basically what this does it checks each cell in the selection and, if it has a formula in it, wraps it within the following construct: =IFERROR(existing formula, 0) The point? Now if an error is returned, it will return 0 instead.

Public Sub WrapWithIfError()

Dim cl As Range

For Each cl In Selection

If cl.HasFormula Then _

cl.Formula = “=IFERROR(” & Right(cl.Formula, Len(cl.Formula) – 1) & “,0)”

Next cl

End Sub

Just a heads up here though… you’ll need Excel 2007 or later to make use of the IFERROR function.

I thought I’d written this down somewhere, but apparently not, so I figured I’d make a blog post out of it.

Despite the fact that it’s been a long time since I’ve blogged, I don’t think it’s a secret that I’m a fan of virtualization. The other day, after posting on some VBAX threads, I started feeling that I needed an Excel 2003 install to test some things. Now I’ve detailed the steps I used to set up an Office Sandbox before, but what I really wanted here was the ability to run multiple VM’s at once. So it was obviously time to recreate my environment.

This time, rather than set up a single virtual machine with all kinds of snapshots on it, allowing me one testing ground at a time, I decided to go with linked clones in VMWare. This allows me to set up one main Windows XP hard drive, then spin off just a linked clone for each Office installation. They’re separate, can run at the same time, and I don’t need to create a large hard drive for each image. Each linked clone expands to incorporate the office install, but that’s about it.

In addition, I also set up a Windows 2003 master and am currently in the process of setting up a domain controller and a file server. This will let me fool around with some features that require a domain, and also means that I can set up a file structure similar to the office. I’m hoping that I won’t have to deal with any more of those “your network add-ins aren’t available right now” messages.

At any rate, here’s the steps I went through to create it using VMWare 6.5.

Initial VM creation

1. I made a new VM and installed Windows XP. Nothing really magical there (at least not in my opinion)
2. Installation of VMWare Tools is also essential

Patching, patching and more patching

1. I installed all the various service packs and windows updates. Tons of them. Then more.
2. Next I installed Microsoft Update (after all, I plan on installing Office here) then checked for even more updates
3. I also installed some optional updates that I thought I might need (.NET 3.5, etc…)
4. Check for more windows updates
5. Basically keep checking for Windows updates until there are none left.

Performance Tuning

1. Download and install BGInfo so that the desktops get tagged with their system info.
   1. Download it from http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx.
   2. I create a folder in C:\Program Files\BGInfo and extract the pieces there
   3. Right click the Start Menu and Explore all Users.  Drill into the startup for all users and place a shortcut to BGInfo.exe there
   4. Modify the “Shortcut to BGInfo” in the statup menu to read “BGInfo”, go into it’s properties and append /timer:0 to the target path
   5. Trash the zip file as it’s no longer required.
2. Install any programs I know I’ll need in all machines (PDFCreator)
3. On Windows Server 2003 uninstall the enhanced browing security.  (Control Panel–>Add/Remove Programs–>Add/Remove Windows Components–>Uncheck Internt Explorer Enhanced Security Configuration.)
4. If space and performance is a concern, uninstall any other unnecessary windows components
5. Go to Internet Explorer and set the Home Page to something I’ll use.
6. Empty the recycle bin
7. Run disk cleanup (StartàAll ProgramsàAccessoriesàSystem ToolsàDisk Cleanup) and clean up EVERYTHING
8. Go into System Properties (Right click My Computer in the Start Menu) and adjust the following
   1. Turn off System Restore (System Restoreàcheck the box)
   2. Adjust for Best Performance (AdvancedàPerformanceàSettingsàAdjust for Best Performance)
   3. Drop the page file (AdvancedàPerformanceàSettingsàAdvancedàChangeàNo Paging File)
9. Restart the computer (to confirm the page file deletion)
10. Run defragmentation in the OS until it is almost instant complete
11. Go back into system properties and create a page file. I set the initial and max size to the Recommended value at the bottom of the page.
12. Shut down the guest OS.
13. Run the VMWare disk defragmentation
14. Uncheck all the “Connect at startup” settings for the floppy drive, CD ROM & Bluetooth

Preparing for next step

1. With the machine shut down, create a snapshot. I label mine “Golden Master” and put in the notes what date it is patched to.

Creating the Office PC’s

1. From the VMWare VM menu, I chose Clone, and set up a Linked Clone from the snapshot
2. Install Office
3. Install utilities like freewheel, mztools, smartindenter. The reason I didn’t do these earlier is that some require office be installed first, and some aren’t necessary (Freewheel) in later office versions.
4. Patch it (until no more patches show up)
5. Run defragmentation in the OS until it is almost instant complete
6. Shut down the VM
7. Run the VMWare disk defragmentation
8. With the machine shut down, create a snapshot. This one I’ll call Office XXXX base image, patched to yyyy-,mm-dd

At this point I’m set up to use that VM. It may sound like a lot of work, but the bonus is that if I can roll back to a fresh office install at any time. Likewise, I can spin off a new linked clone from my Golden Master at any point in time too.

You’ll notice that I don’t have any antivirus software on the VM’s. This is because I’m not using these for production, just for testing. I don’t really want to have huge AV downloads every time I want to make a quick test, and I don’t plan on surfing the net on these machines.

As far as Windows servers, I basically follow the same steps. Once I’ve got the base OS installed, patched and tuned, then I snapshot the golden master, create the linked clones, and off I go.

I know it’s been forever since I’ve blogged here, and I’m sorry to have to revive this with a rant, but…

I was writing an article up for how to customize the Office Ribbon in Excel 2010, and thought I’d build a custom Auditing Tab. Using the built in groups is REALLY easy, but there were commands there I didn’t need. So I figured that I’d try to make some custom groups, insert the commands I wanted, and see how it worked. Even better, I know that you can now export the customizations, so I could even share them!

To build a custom Ribbon tab:

• Right click the Ribbon and choose “Customize Ribbon”, or go to FileàExcel OptionsàCustomize Ribbon.
• Click New Tab
• Select the tab and choose Rename
• Go to the “Choose commands from” dropdown and choose Main Tabs
• Find the groups you like, select them and click the Add>> button

To add a new group to a tab, first select the tab then:

• Click New Group
• Select the group and choose Rename
• Drill down to the commands you like (left window), select them and click the Add>> button

Overall it’s not too complicated really.

So here’s the setup I did for my Auditing Tab… (I shrunk it so that you only see stuff pertaining to that tab):

As you can see, I created custom Formula Auditing, Other Tools and Sort & Filter groups. (I don’t know why I use the default Sort & Filter as well a custom one, but there you have it. At any rate, it doesn’t change this… the tab looks like crap!

The icons for Trace Precedents, Trace Dependents and Remove Arrows are all large and very blocky looking. Same with the Sort buttons on the custom group. Why is it that they are shown nice and small on the built in group, but not the custom one? And isn’t it interesting how Microsoft broke the 1 or 3 to a row rule with the Sort group? Makes it very obvious here!

If you want the customization file to try this yourself, click here.

Personally, I’m happy enough with the way the built-in groups work, but I think there should have been a bit more control on the sizing of the icons here. It seems that the need for my book (RibbonX – Customizing the Office 2007 Ribbon) isn’t dead yet, but honestly, it should be!

Tonight I discovered that my site had been hacked… as it turns out I was running an old version of Wordpress and there was some injection code fed into a security vulnerability. I’ve upgraded to the latest version, which has cured that issue, but in the process some things seem to have occurred:

• I’ve got some funny looking code samples
• I’ve got a weird A character showing up all over
• I’ve lost my navigation to the RibbonX TOC

I’ll try and fix this up over the next few weeks (kind of busy), but my main priority is going to have to be my main site, which still has some issues and needs a pretty major upgrade…

Sorry folks, I intended to post this yesterday, but I’ve been having issues posting to my blog from Word.  Somehow it’s just stopped working.  I have a full post ready to go, but it’s just not publishing for some reason, from either Word 2007 or 2010.  It would seem to indicate that the issue is server side, so I’m going to need to work that out.

At any rate, the winners of a free license for WinAutomation, randomly generated from the list of entries are:

• Dennis
• Sam
• Eric

I’ll be in touch to let you know how to claim your licenses.

Hopefully I’ll be able to get my posting abilities back from Word, as this is a bit odd.

A few weeks ago I received an email which read in part:

I’m writing to you to make you aware of WinAutomation (www.WinAutomation.com), our software product (that includes a powerful macro recorder) that helps you automate your repetitive tasks (e,g. files, folder, database, web actions etc), so that you can save time, effort and labor.

In the name of full disclosure, the person offered me a pro version of their software if I’d like to blog a review of it. Actually, they even went further and offered me some licenses to give away to blog readers as well. It sounded like a pretty good deal to me, so I accepted.

Now, before I really give a review on this, I think it’s worth acknowledging that I’m not your typical user. When I feel like automating a task, I typically write the code to do it. I’d also say that the majority of those tasks are inside office, and I do very little in the way of repetitive tasks at the Windows Shell level.

WinAutomation does offer the ability to automate Excel to some degree and has an “Execute SQL Statement” ability to work with databases. Neither of those are any interest to me though, as I’d do both through VBA. I really don’t do any repetitive web actions either… unless you count opening bookmarks. So what could this offer me?

Basic Scripting

One thing that I was looking to accomplish was file sharing. I use Windows Live Mesh to write a critical file up from my laptop to the cloud and share it with a couple of people. The problem I had is that this file also needs to exist on the network, and I don’t want to install Windows Live Mesh on my file server. So I needed to come up with a way that I could copy this file to the network every time it was changed. WinAutomation does actually accomplish that need for me.

The interface doesn’t take too long to get used to, and in a short time I was able to knock up the following script:

Long story short, this checks if my network exists and copies the file to the network if it does, or notifies me if it isn’t copied. The script is set up with a trigger to monitor the source file and runs each time the file is changed. And I get a notification popup with the result:

This is pretty cool for me because LiveMesh syncs the file to the cloud, and WinAutomation copies it to my local server. With a Lenovo feature I can run an exe each time I log on to a specific network profile, and WinAutomation can compile the macro to an exe file for me.

Of course, if I don’t want to go the exe route, I can set up a trigger in WinAutomation to monitor the system’s “Application” event log and run the script each time a certain event is triggered. It gets a little tricky though, as you need to find an event that fires consistently after WinAutomation starts up, but it seems to me that the WinLogon 4101 event should work:

Variables

I am sorely disappointed in the variables that are offered. In the job above I really wanted to just set the files to variables at the beginning of the routines so that I could check properties of the files. I couldn’t seem to do that, though. For example I set a file into a file variable and a folder to a folder variable. Then I wanted to check if the file existed inside the folder… it was a non-starter, and pretty frustrating.

Macro Recorder

WinAutomation also has a Macro recorder. I tried recording a macro which:

• Opened Notepad
• Typed in some text
• Opened Outlook

The results are below.

Honestly, I was a little disappointed in this. Maybe it’s because I’m used to using VBA and controlling an Object Model, maybe because I actively avoid using SendKeys… I picked Notepad off my “Most Recent” list, so I really wonder what happens when it changes position. Based on what I see below the macro would stop working… I don’t claim to be smart enough to offer a solution though, but then I didn’t write the program.

Recording file/folder actions through Windows Explorer yields the same issues with mouse clicks and SendKeys as those above. I find it a little weird that there is no warning to the potential of failure if the window wasn’t open in exactly the same place.

I can give an example of where the macro recorder is pretty useful though, despite the limitations above. You can set up a keyboard trigger (CTRL + ALT + SHFT + F for example). This worked well for some data entry I had to do when I was fixing a loyalty program setup today. I had to enter a category number and then enter data in about 12 columns for each category. The rub was that only the category number changed, and the rest was consistent data. So I recorded a quick macro, hooked it to the keyboard shortcut and then just had to enter the number and hit the key combo to fill the line. Much better.

Setting Triggers

The program certainly does have some power to it, and the selection of objects that you can program is fairly large. I didn’t have too much trouble creating jobs through the designer to do most of what I wanted, although working with the variables seemed pretty clunky to me.

The hardest part of working with this software though is coming up with the appropriate trigger for your macro. Here’s a list of the available triggers for completed jobs:

So the thing is that you need to get creative with how you set up the trigger. And what you want may not actually be there, so you may need to look at another route. For example, I thought about setting up one job with a ping trigger… but the ping trigger here only fires when the host doesn’t respond. I kind of wanted it where the host did respond. So I’m left hunting for another way to figure out if the server exists.

One case in point… Assuming that you are not using Windows 7 or a Lenovo computer, (both have built in functionality for this,) you may want to set a different default printer when you connect your laptop at home vs work. Creating the actual macro is super easy as you can see:

The real question lies in how you set the trigger… one way is shown below:

In browsing my event logs though, it looks like you can have multiple entries in the event log for WinLogon, so maybe it’s better to monitor a specific outcome. This one will kick off each time the Windows license is validated, which I think only happens right after you log on:

At any rate, setting triggers is the toughest part of this program. It takes a bit of getting used to, and can be pretty frustrating when you first start. A little persistence pays off though, and really starts to unlock some deep power.

Conclusion

I went through some ups and downs as I was learning to work with WinAutomation, with it sometimes feeling impossible to make it actually return the true potential it should offer. Building macros is actually very easy using the drag’n'drop interface provided. But the real key is to not give up on the triggers portion and look at things in different ways until you find one that works.

To me the “SendKeys” style output of the macro recorder is a big disappointment, but despite that, it still offers the ability to knock up some quick data entry features in programs that don’t otherwise support any automation. That value cannot be overstated as it can save you tons of time.

The more I work with this program, the more I do find uses for it.

Free Licenses!

So… would you like to try it? They have generously provided me with 3 licenses to give away.

Here’s how it works…

1. Send me an email at ken at excelguru dot ca with the following subject: “WinAutomation Giveaway”. (If you don’t get it right, then you won’t be entered. )
2. I’ll take all the valid entries received between now and midnight Pacific time on Tuesday, October 27, 2009
3. All valid emails within the timeframe will be entered in an Excel spreadsheet
4. Each entry will get a RAND function beside their name
5. I’m going to sort them, and whomever comes out in the top three wins

Over the past… ages… it seems like I haven’t been able to my site, but I got a new article up today. It’s a variation on my Count Files (with a specific extension or not) in a folder entry. It doesn’t seem at first like a big departure, but I’ve posted a version now that will also count files in both the folder and subfolders. It was an easy fix, requested by a reader. The big difference is really that it uses recursion to keep calling itself to dig deeper in the folder structure to get the info.

At any rate, if you’re interested, you can find it here: http://www.excelguru.ca/node/106

One of the things I really encourage my staff to do while they work here is to create complete procedures manuals for their jobs. We’re talking about full documentation with step by step instructions on how to accomplish their tasks, complete with screenshots where applicable. And while many new staff members may find this a little strange, they quickly realize the worth when they can pick up a manual from their predecessor and start to work through their new job. So far I don’t think I’ve had anyone who has dismissed the value of these documents.

The concept also plays into other areas in our systems which probably won’t come as a surprise to anyone:

• If someone is sick, a co-worker can pick up for them while they recover,
• When training, it takes less time to re-familiarize yourself with the material to show the person,
• Often time you can let the person work through stuff on their own without impacting your time, and
• In slow periods there are always manuals that need to be updated, so there is less pressure on your time trying to find someone meaningful work to do.

I’m also sure you could come up with a few more points, but I figure those are enough to keep the motto of “Document It” alive in my workspace.

There are, however, some dangerous drawbacks that can come in to play here, and these typically surface when someone leaves the organization. That list includes, but certainly isn’t limited to the following:

• Procedures that have changed, and the manuals not updated to reflect those changes,
• Multiple copies of procedures being left in the manual,
• Not being able to find the appropriate manuals, and
• People “going through the motions” in their job without ever really understanding what they’re doing.

The last one is, of course, the supervisor’s job to identify and resolve, so I’m not going to focus on that at all right now. I’m more interested in the first points.

We recently had someone leave us. She’ll be missed, of course, but my team has really bonded and pulled together to make things happen. Fortunately in many cases we were able to simply pull the book off the shelf and run, but there were a couple that gave us pause…

We naturally rooted through binders and other files and ended up coming up with multiple procedures in some cases, or not all in others. Then the question surfaces on which is the correct procedure, or if any of them are. In our case it wasn’t always obvious, so we had to spend time working through them to tell. Naturally this kind of defeats the purpose.

In the cases where the procedure is out of date, (either because it subtly grew over time, or radically changed and new documentation wasn’t yet prepared,) it needed revising… but where was the original document to make those changes? Was it stored in a common folder, private folder, semi-private… who knows? In some cases these procedures had been written by a former staff member and their binder was divided up between people when they left… so now we have procedures that may be stored anywhere on our network.

This got me thinking about how we organize and store our manuals, as well as updates. We could use one specific folder on our network, but then we get into issues with naming conventions and finding the documents within the subfolders as well. “No,” I thought… “the perfect solution would actually be a Wiki!”

A wiki, by its very nature is a collaboration tool that allows shared editing, complete with versioning and rollback features. This would be great for us, as we could store the entire set of procedures in one common place, share it among all our accounting staff, and let them edit the entries if they found that they were out of date. (Of course we’d encourage them to keep them up to date, but let’s face it… we’re all human.)

I figured this would be an easy slam dunk, actually. We have Windows Sharepoint Services 3.0 (WSS3.0) installed on one of our servers, although we’ve never used it in earnest. I installed the default Wiki template that comes with WSS3.0 and gave it a run…

It SUCKS! I mean REALLY sucks!

The whole point behind this, to me, is to make it really easy to create rich entries. It looked good at first, when I went to edit a page and saw the nice editing toolbar:

So I started typing away, then went to insert a picture:

Wtf? I was seriously hoping for a Browse button, not to have to hand type in a URL. Not to be discouraged, I tried to paste the picture directly into the text, but it was a no-go either. The route you have to go for each picture you want to insert is:

• Snap the shot
• Save it as an image
• Navigate to the Sharepoint Image Library
• Upload the picture (and optionally name it)
• Copy the link to the picture
• Navigate back to your entry
• Fill in the appropriate URL info
• Hope it works when you click OK. (My test did not.)

Doesn’t that seem just a little awkward? I need to put this into the hands on non-programming type folks. I think the concept of the Wiki has a LOT to offer us, but the implementation here is brutal!

When I’m blogging to Wordpress from Word I can just write up the document in Word, complete with pictures, and just publish it. It auto-magically uploads, resizes and embeds them for me. Sweet and simple. Why can’t this be the same? Or can it?

Despite my searching for over an hour, I have not been able to find another Wiki template that works with WSS 3.0, or a webpart that will allow for easy picture uploads. If anyone knows of one, I’d love to hear about it. I’m not above actually paying for them either, but I’d prefer to take them for a test drive first, as I’ve committed to my staff that we won’t go this route unless it is user friendly. They don’t want to become programmers, and they shouldn’t need to.

Our requirements are actually pretty simple. We need:

• A simple text editor capable of:
  • Basic formatting such as Bold, Italics, Underline, Colors
  • Controlling font size for headings
  • Bullets
  • Numbering
  • Tables
  • Creating hyperlinks (without having to hand write HTML code)
  • Adding pictures in-line with text (without having to do the dog and pony show I described above)
• A decent search engine
• A Simple method for linking new pages into the Table of Contents
• Versioning
• It to work with Windows Sharepoint Services 3.0 (WSS3.0)

Bonus points for:

• Being able to author the initial copy in Word
• Styles
• Workflows for approval of new/edited entries
• RSS for updated entries

If anyone has any thoughts on this, I’d love to hear them.