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:

  1. Sub PrintToPDF_DualCopy()
  2. 'Author       : Ken Puls (www.excelguru.ca)
  3. 'Macro Purpose: Print multiple worksheets to individual files and write the output to two locations
  4. '   (Download from http://sourceforge.net/projects/pdfcreator/)
  5. '   Designed for early bind, set reference to PDFCreator
  7. Dim pdfjob As PDFCreator.clsPDFCreator
  8. Dim sPDFName As String
  9. Dim sPDFPath As String
  10. Dim sPDFPath1 As String
  11. Dim sPDFPath2 As String
  12. Dim lSheet As Long
  13. Dim lLoop As Long
  14. Dim bRestart As Boolean
  16. 'Set your PDF Paths here:
  17. sPDFPath1 = ThisWorkbook.Path & Application.PathSeparator
  18. sPDFPath2 = "F:\Some Folder\"
  20. 'Activate error handling and turn off screen updates
  21. On Error GoTo EarlyExit
  22. Application.ScreenUpdating = False
  24. Set pdfjob = New PDFCreator.clsPDFCreator
  26. 'Check if PDFCreator is already (or still) running and attempt to kill the process if so
  27. Do
  28. bRestart = False
  29. Set pdfjob = New PDFCreator.clsPDFCreator
  31. If pdfjob.cStart("/NoProcessingAtStartup") = False Then
  32. 'PDF Creator is already running.  Kill the existing process
  33. Shell "taskkill /f /im PDFCreator.exe", vbHide
  34. DoEvents
  35. Set pdfjob = Nothing
  36. bRestart = True
  37. End If
  38. Loop Until bRestart = False
  40. For lSheet = 1 To ActiveWorkbook.Sheets.Count
  41. 'Check if worksheet is empty and skip if so
  42. If Not IsEmpty(Worksheets(lSheet).UsedRange) Then
  43. sPDFName = "testPDF" & Sheets(lSheet).Name
  44. With pdfjob
  45. '/// Change the output file name here! ///
  46. .cOption("UseAutosave") = 1
  47. .cOption("UseAutosaveDirectory") = 1
  48. .cOption("AutosaveFormat") = 0    ' 0 = PDF
  49. .cOption("AutosaveFilename") = sPDFName
  50. .cClearCache
  51. End With
  53. For lLoop = 1 To 2
  54. If lLoop = 1 Then
  55. sPDFPath = sPDFPath1
  56. Else
  57. sPDFPath = sPDFPath2
  58. End If
  59. pdfjob.cOption("AutosaveDirectory") = sPDFPath
  61. 'Print the document to PDF
  62. Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
  64. 'Wait until the print job has entered the print queue
  65. Do Until pdfjob.cCountOfPrintjobs = 1
  66. DoEvents
  67. Loop
  68. pdfjob.cPrinterStop = False
  70. 'Wait until the file shows up before closing PDF Creator
  71. Do
  72. DoEvents
  73. Loop Until Dir(sPDFPath & sPDFName & "*.pdf") = sPDFName & ".pdf"
  74. Next lLoop
  75. End If
  76. Next lSheet
  78. Cleanup:
  79. 'Release objects and terminate PDFCreator
  80. On Error Resume Next
  81. pdfjob.cClose
  82. Set pdfjob = Nothing
  83. Shell "taskkill /f /im PDFCreator.exe", vbHide
  84. On Error GoTo 0
  85. Application.ScreenUpdating = True
  86. Exit Sub
  88. EarlyExit:
  89. 'Inform user of error, and go to cleanup section
  90. MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
  91. "has been terminated.  Please try again.", _
  92. vbCritical + vbOKOnly, "Error"
  93. Resume Cleanup
  94. End Sub

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:

  1. Application.Speech.Speak Target

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

  1. Sub Count()
  2. Dim lCount As Long
  4. For lCount = 1 To 100
  5. With ActiveCell
  6. .Value = lCount
  7. .Offset(1, 0).Select
  8. End With
  9. Next lCount
  10. End Sub

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!