We've had a few forum questions recently asking how to play audio files in Excel.Normally I'd just use my media player for this but I can see that you might want to have an audio or video file that may explain something about your workbook. Or you could use Excel to play videos in order to do some training of staff.
The ability to do this kind of thing is there, so in this post I'm going to show you how to do it.
You're going to need to insert some ActiveX controls so you will need to enable the Developer tab if you haven't already done this.
I'm going to use the Windows Media Player control, and a Command Button we can click to move to the next audio/video file.
To insert these, from the Developer tab, click on Insert, then More Controls. From the pop-up window, scroll down to Windows Media Player, select it and click OK.
You'll now see that your mouse pointer has changed to a cross. Click on the sheet and drag the box to the size you want the player to be.
Using a command button will allow us to click on it and load the next audio/video file. Click Insert, click on the ActiveX Command Button, and drag it to the size and position you want.
Editing Control Properties
By default the name and text on the button are CommandButton1, I want to change this. First, make sure you are in Design Mode.
On the Developer tab, Design Mode mode should be colored/highlighted to indicate that you are in Design Mode. When this is the case the controls won't carry out their usual functions.
Right click on the button and click on Properties. In the Properties window, I've changed the caption to 'Next' and changed the button's name to 'NextMediaFile'. We use the name in our VBA code so it's important to have something meaningful.
Loading Media Files into the Player
We'll come back to the VBA that loads the media files in a second, first I want to look at how we know what to play.
What I have done is use a specific cell, A3, to tell me the folder where the files are, and below that is a list of the files I want to play.
Please note that the filename must include the extension.
You could create a playlist in Windows Media Player and load that, and it will play everything in the playlist.
We want to click the 'Next' command button to load the next file so to do that we need a little VBA
Make sure you are in Design Mode, and right click the button. Click on 'View Code' and the VBA editor will open with an empty sub already created for you.
Note that the name of the sub is taken from the name we gave the button. Now you just need to enter the code to do the fancy stuff.
Download the VBA & Workbook
Enter your email address below to download the sample workbook.
How the Code Works
Each time the button is clicked the ActiveCell is moved down one row.
The code uses the ActiveCell to load the next file. If that cell is empty it goes to the start again, cell A4.
The file listed in the ActiveCell is loaded into the media player.
The code makes the assumption that there isn't anything else on the sheet, so if you happen to have F34 selected, it won't have anything in it. That being the case, the code will go back to the start of the media files and play the first one.