Coding Education STEM

Cataloging system audio files and playing them back

In this blog, I demonstrate how to enumerate all the system audio files that comes with Windows, put the list in an Excel file. Then use that list to play each one with a click of a button.

First, let’s look at generating the list of audio files in our system folder. They’re in windows\media subfolder, or technically in SystemRoot\Media where SystemRoot is the environment variable already set in your Windows OS.

The code is as follows:

Pretty straightforward. Builds the location path where to look (e.g. “Windows\Media”) and using a For loop, we iterate through the content of the folder and get each file’s name and the full qualified name (including path). Each time, we place each filename and its qualified name in first column (i.e. A), and next to it, (i.e. B) in an open Excel sheet.

To make it work, save the code in an external text file. Open Excel and create a new blank sheet. Then launch the Visual Basic Editor (VBE) and import the code file, and run it. The blank sheet will be populated with all the information. Here are the actual steps in sequence:

As you can see, the sheet was blank, and once the code is ran, the sheet gets populated.

Ok, the next step is to actually play the sound. Playing an audio file is pretty simple in Excel. The code is below, but remember that for 64-bit OS (which I used), you must include the PtrSafe attribute with declaration. The sndPlaySound built-in function takes the full name of the audio file and the 2nd parameter specifies how to play it. I chose Asynchronous mode. For rest of the options, search in Excel or Office help.

Again, I saved this code in an external code file as text. Then imported it into the sheet we created previously.

Next, I saved that sheet as XSLM as we’ll need a button to play the audio files in the sheet and it must be macro-enabled.

From Developer tab, I inserted a button control, added the text and hooked up the play code to the button. You do this by right-clicking the button and assign a macro (choosing the macro you saved and imported above).

Now everything is set. Click on a cell in column B (column A won’t work as the sndPlaySound command requires the full path) and click the Play Selection button…and you’ll hear the sound for that selection.

Let’s see it action…

This video clip shows the selection and actual execution of the playing code.

The code can be wrapped into your custom functions to do other validations and play any supported audio file.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top