PowerPoint: Why is my video playing as a white box?

Here's an interesting little problem. You've inserted a video into a slide of your presentation, positioned it where you'd like it, then run your presentation and... it doesn't work. It's showing up as a white box. What's the deal?

You try playing the video outside of PowerPoint, and it works just fine. You check to see that it's in a PowerPoint-compatible video format, and it is. So why won't it play?

The answer is this: the filepath for the video is too long. PowerPoint can only handle media files with filepaths of 128 or fewer characters. So if the video is hiding away in C:\\A\Very\Obscure\Folder\Somewhere\Deep\Inside\Your\Computer\Or\Perhaps\With\A\Ridiculously\Long\Name, PowerPoint will be unable to locate and play the video in the presentation.

Solutions?

One would be to move the video file to a location with a smaller filepath. Or, move it to the same folder in which your PowerPoint presentation is saved. Even if the filepath is still more than 128 characters long, the video file will play if it's in the same directory as the PowerPoint file.

N.B.: This isn't the only problem that can occur with videos not working in PowerPoint, and I will address some other common issues in future posts or as requested.

Excel: Count the Number of Words in a Cell

Yet another great question came to me by e-mail this evening: "how do I count the number of words in a given cell?".

You can get this answer using a formula! Here it is (please substitute 'A1' with the cell reference of your choosing):

=LEN(TRIM(A1))-LEN(SUBSTITUTE((A1)," ",""))+1

And for those of you that are curious as to what exactly this formula is doing:

LEN returns the number of characters.
TRIM removes extra spaces at the beginning or end of the string.
SUBSTITUTE replaces the character of your choosing with something else. In this case it's replacing spaces (" ") with nothing ("").

So, this formula is taking the number of characters in cell A1 (disregarding any extra spaces at the beginning or end), and subtracting from it the number of characters in cell A1 if it had no spaces. Add one and you get the number of words.

Wait, what?

You're right, I'm not very good at explaining this one. So I'm going to show you an example:

If cell A1 contains "Stacy DuBois", the formula is taking 12 (how many characters there are) minus 11 (how many characters there are without the space in between my first and last name). That gives you 1. Add 1 and you get 2. That's how many words are in that cell. This will work with any number of words in a cell. Ah, the wonders of math!

PowerPoint: Supported Video File Formats

Tried to insert a video in PowerPoint and it didn't work? Maybe it was in a file format that isn't supported by PowerPoint. Here's a list of supported file formats:

Motion Picture Experts Group
.mpg
.mpeg
.m1v
.mp2
.mpa
.mpe

Microsoft streaming format
.asf
.asx

Microsoft Windows Media Video
.wmv

Audio Video Interleave
.avi

QuickTime (v1 or v2.x)
.mov
.qt

If you have a video in any other format, I'd suggest finding and using software to convert it to one that will work in PowerPoint.

OneNote: Can you print the ruler lines?

A fair amount of digging has revealed to me the unfortunate fact that no, OneNote does not include the option to print the ruler lines on your pages. The only workaround I can see is to actually insert an image of ruler lines on the page yourself, and put your content over it. That's kind of sloppy though, isn't it?

Maybe if we all write nice letters.

Dear Microsoft:

Please let us print ruler lines in the next version of OneNote. Some of us really like the way they look both on and off the screen.

Thanks,

Stacy

Access: Bulleted Lists

This week's question comes all the way from Germany! I like that I've got people coming to my blog from all around the world, that's pretty neat.

"How do I make bulleted lists on my Access reports?"

This is a very good question! You'll notice that, unlike Word, there's no easy option on the ribbon to make bulleted lists. Thankfully, there's a somewhat simple workaround for that.

Go to design view for your report. In the detail section, go to the text box of the item you'd like to appear in a bulleted list format. As an example, let's say that the text box currently contains ItemName. Now, you're going to trick Access into making bullets next to this item by concatenating a bullet character, a space, and the ItemName. Here's what you'll want to enter into the text box:

= Chr(149) & " " & [ItemName]

Don't worry! It looks like mumbo jumbo, but I promise you it'll work. Chr(149) is the code for a bullet character (like this: • ), then it's putting in a space, and finally the ItemName. If the concept of concatenation is foreign to you, I suggest taking a look at this post where I explain concatenation in Excel.

As a sidenote, if you want to just type out that bullet character (in Windows, anyway), all you need to do is type Alt + 0149. Cool, eh?

Word: Open Documents As Final (Without Reviewing Markup)

The last question I got, a very good one, was from a very frustrated guy who was sick of opening up his Word documents and having them appear with all of his reviewing markup showing (additions, deletions, comments, etc.). He wanted a way for it to automatically show the 'Final' version. And here is a solution for you! But fair warning, if you use this bit of VBA code, it will not immediately be apparent if changes have been tracked in a document and if there are comments and other markups in the file. Why might this be a problem? If you're planning on sending this file on to someone outside of your company, they might be able to see all of the changes you've made as you've been working on the document... which isn't so great. So please, if you use this code... make sure you accept all changes made and remember to delete your comments before sending it off.

Obviously that doesn't apply if you're printing or making a PDF of the document.

Anyway! On to the code. I'm writing this post with the assumption that you know how to add a macro to your "Normal" template in Word. If this sounds foreign to you, I suggest doing a quick Google search and learn a bit about macros and VBA code in Word. Or, if you're patient, you can wait and I'll eventually make a post explaining that stuff.

There are two codes here, as there are two instances when you might want Word to automatically switch the view to 'Final': the first being when you open an existing document, and the second being when you create a new document.

Sub AutoOpen()
'Sets the revisions view to 'Final' when opening a document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub

Sub AutoNew()
'Sets the revisions view to 'Final' when making a new document
With ActiveWindow.View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With
End Sub

Happy New Year, my friends! Here's hoping your 2010 is a safe, happy, and healthy year for you all.