The Ken Puls (Excelguru) Blog

I just recently taught my course on “Building Spreadsheets That Last”.

In it, I actually show a video that was written by Ross on Workbook Structure protection. I like Ross’s video, as it shows some interesting tricks with the features. You can find Ross’s video on YouTube here. (Sorry Ross, tried to point it to your blog, but can’t find the post there any more!)
A while back, my RibbonX co-author Robert also recorded some videos.

• One on using conditional formatting to highlight the active row
• One on changing the registry

I’m curious to know what people think of videos in the training scope. Do you prefer books, videos, forums, other things, or a combination of all of the above when you are learning?

I’ve always been someone who likes to get a book in their hands (or a whitepaper), then try to get into things by hand. As I find myself teaching, though, I’m starting to wonder if delivering a more complete package is in order. I already give a live presentation, with a case study to work through, but I’d also like to write up an e-Book of my most popular course. The big question is… should I be also contemplating adding video to it? Does anyone like that option, or is it just a move toward being pirated?

I received a question from a reader of my site about how to adapt my Print Multiple Worksheets to Multiple PDF Files article so that it would also print each file to two separate directories.

Some important things to notice about this code:

• It was developed using PDFCreator 0.9.3, and may not work with 0.9.5 as some of the Object Model in that version is different.
• You need to set a reference to the PDFCreator library, as this routine is developed to work with an Early Bind

In addition, this routine incorporates some different methods from my original routines posted on my site, including:

• It attempts to shut down PDFCreator if it is already running when the routine is launched, so that it can create a fresh copy
• It uses a different method to test for completion.  Namely, it tests when the file shows up at the target location, rather than testing to see when the PDF queue is empty.  (This has inherently been more reliable than the method I originally used.)

Now, I just need to find the time to update all the existing articles on my site, and start playing with version 0.9.5 for Vista…
Note:  PDFCreator 0.9.3 doesn’t work on Vista, and the taskkill method may not work on XP Home.

A while ago I received an email from Microsoft TechNet from “Hey Scripting Guys” where they showed how to script Excel to put change the background colour of a cell depending on the value. Basically, they reinvented the wheel, as they could have used conditional formatting for the task, which would have been more efficient. I was so disgusted that I sent it off to Simon Murphy, who posted a bit of a rant on it too.

Well, this time I’m actually going to post with a bit of Kudos for them… I recently picked up my January copy of TechNet magazine, and read the Hey Scripting Guys column on using Regular Expressions (RegEx).

I’ve been meaning to get into RegEx for quite some time. Matt Vidas reminds me every now and then that I should, and I know that Dave Brett is a huge fan of them. Unfortunately, I’ve never seen any easy entry point into the topic… until now. These articles were actually pretty easy to follow. The focus was pretty simple, but covered the basics of identifying patterns in text strings.
Then, much to my surprise, in the May edition of the column, they took the subject on again. This time the focus was on replacing certain patterns within text strings, rather than just identifying them. While the examples were pretty simple, you can certainly see the power of them.

I’m putting this a little higher on my list now, as I can see some use for this in my own work, which means I might be able to get to it in a couple of months or so. LOL!

If you’re interested, the articles I read are listed below. (And if you’re not, then the links are for me when I get back to actually working with this. )

• January 2008 – There’s No Mistaking Regular Expressions
• May 2008 – Raising Eyebrows on Regular Expressions

Okay, I’ll admit that you probably have to be a parent of a young child to agree with this, but here it is:

This all started when I showed my kid how fast the computer could count to 100 with this code:

We then moved up to “Wow, Daddy! Can it go to 1000?” Then eventually it was a contest to see if she could get her pyjamas on faster than the computer could count to 10,000.

This weekend, though, when testing out my new article on Having Excel Read a Range of Data to You, (written for someone who emailed me through the site,) I made the computer count to 10 out loud.

She was fascinated. Then we started with words…

The conversations got more complex line by line, with her talking to “Laptop”. It took over 178 lines of conversation until Laptop finally sent her to bed. Great fun! (I will admit that I have a little fear that I just turned my laptop into a new game though!)

This is a big irritation…

I’ve started getting errors every time I open an Office application.  Specifically, the error message reads:

“EXCEL.EXE – Bad Image

C:\Windows\system32\mscms.dll is either not designed to run on Windows or it contains an error.  Try installing the program again using the original installation media or contact your system administrator or the software vendor for support.”

After clearing that message three times, it comes up with a final error message entitled:

“EXCEL.EXE – Unable to Locate Component

This application has failed to start because mscms.dll was not found.  Reinstalling the application may fix this problem.”

Grrrr….

For reference, the application (be it Excel or Outlook, and I haven’t check the others) still open and seem to work normally.

So here’s what I know:

• I added an IMAP account to my Outlook settings, synching up my gmail account to Outlook
• I installed Visual Studio 2008
• I installed PefectDisk 2008 (with VMWare))

I believe the problem surfaced when I turned off IMAP and deleted my IMAP account from my Outlook profile.

I’ve tried the following steps to fix the issue:

• Running a Repaid installation on Office 2007
• Running regsvr32 on the file (it does still exist)
• Uninstalling Office and reinstalling
• Running a complete Registry cleanup (as administator) using CCleaner

So far nothing has worked, and I can’t find anything remotely hopeful on the net.  In fact, the only reference I’ve been able to find about this issue so far has been unanswered public ng threads for Outlook.

Suggestions welcome…

In our Microsoft migration a couple of months ago, we moved from Novell’s Groupwise for email to Microsoft Exchange 2003, using Outlook 2007 as a client.  I knew things would be different, but some things just make me shake my head.

My boss just took a week off, and set up an “Out of Office” reply using Outlook’s Out of Office assistant.  He then sent an email to himself from home… and no reply.  Strange…

So I ask my tech consultant to look into it, thinking that something must not be configured correctly on the server.  In the mean time, I forward my boss an email from my work address… and get a reply saying he’s on vacation.  What the heck?  Now I’m thinking… “Did it fix itself overnight?”  So I send an email to him from one of my many external email addresses, but no luck.  Still broken.

I’ll admit that this seemed pretty strange.  It works internally, but not externally.

Then my tech comes back to me with this Microsoft KB entry, which says:

“By default in Microsoft Exchange Server 2003, the sending of out-of-office reply messages to the Internet is disabled. Many administrators do not allow out-of-office reply messages to be sent outside the Exchange organization to prevent unauthorized people from learning when users are out of the office.”

What the hell?

Privacy is great, really.  But for crying out loud, this is overboard.  Turning off replies to the internet is the default?  Come on!

Give the paranoid system admins a way to turn the feature off if they’re that concerned.  But there is no reason in the world that you should force 90% of users to turn on the feature to protect the 10% of users that deliberately neglected to tell their wife they were taking the day off! Honestly, all the people in the office already know he’s gone, responding to them is ridiculous.  It’s the ones emailing him from elsewhere that need to know!