Excel Videos

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.

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?

PDF Creator – Multiple worksheets to multiple files in two folders

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.

There are at least a couple of ways to do this, but the one that I worked up is shown below:

[code]Sub PrintToPDF_DualCopy()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print multiple worksheets to individual files and write the output to two locations
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim sPDFPath1 As String
Dim sPDFPath2 As String
Dim lSheet As Long
Dim lLoop As Long
Dim bRestart As Boolean

'Set your PDF Paths here:
sPDFPath1 = ThisWorkbook.Path & Application.PathSeparator
sPDFPath2 = "F:\Some Folder\"

'Activate error handling and turn off screen updates
On Error GoTo EarlyExit
Application.ScreenUpdating = False

Set pdfjob = New PDFCreator.clsPDFCreator

'Check if PDFCreator is already (or still) running and attempt to kill the process if so
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator

If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running.  Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False

For lSheet = 1 To ActiveWorkbook.Sheets.Count
'Check if worksheet is empty and skip if so
If Not IsEmpty(Worksheets(lSheet).UsedRange) Then
sPDFName = "testPDF" & Sheets(lSheet).Name
With pdfjob
'/// Change the output file name here! ///
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveFormat") = 0    ' 0 = PDF
.cOption("AutosaveFilename") = sPDFName
End With

For lLoop = 1 To 2
If lLoop = 1 Then
sPDFPath = sPDFPath1
sPDFPath = sPDFPath2
End If
pdfjob.cOption("AutosaveDirectory") = sPDFPath

'Print the document to PDF
Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
pdfjob.cPrinterStop = False

'Wait until the file shows up before closing PDF Creator
Loop Until Dir(sPDFPath & sPDFName & "*.pdf") = sPDFName & ".pdf"
Next lLoop
End If
Next lSheet

'Release objects and terminate PDFCreator
On Error Resume Next
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub

'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
"has been terminated.  Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub[/code]

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.

Hey Scripting Guys… on Regular Expressions

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. 😉 )

The best line of VBA ever

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

[code]Application.Speech.Speak Target[/code]

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

[code]Sub Count()
Dim lCount As Long

For lCount = 1 To 100
With ActiveCell
.Value = lCount
.Offset(1, 0).Select
End With
Next lCount
End Sub[/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!) 😉

mscms.dll error

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."


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...

“Out of Office” reply is a privacy concern?

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!