Embedding and accessing a file in Excel with VBA and OLE Objects

I came across a problem recently in trying to improve my spreadsheet by including accompanying files, and utilize them in VBA at the same time.

For example, what if I wanted to play a “success.wav” sound file when a user successfully completed a task in the spreadsheet? I’m not talking about the issues surrounding using VBA to play a sound file —that’s well documented on the web. What if I wanted to embed the sound into my spreadsheet, and play it within VBA, without the user ever noticing there were a file? This is important for a typical office scenario:

If you have your Excel tool, but it requires a bunch of other files for it to function nicely, wouldn’t it be annoying to have to distribute all these files with your tool too? Is there a way we can avoid sending .zip files and messy extractions, and have a self-contained tool?

1. Embedding your files

Excel actually offers a very nice way to embed files into your sheet very easily. This feature is called OLE Objects, and it’s simple to do. In the “Insert” tab of your ribbon, under the “Text” section, there’s a button for “Object”. From here, you can use the “Create from File” tab to embed your file.

Your embedded object will now appear as an object within the sheet. This gets us halfway through the problem: now we don’t need to worry about all those files alongside our tool, but how do we go about accessing these embedded files?

2. Accessing Embedded files

So, since we’re using a built in way to embed files into Excel, surely Microsoft have provided a nice VBA api to access these embedded files to be extra helpful? Bwaha, don’t be silly.

As I learned, embedding of files and OLE objects in general were implemented in an ancient version of Office as a hacky way to do stuff back in ActiveX. Support for them was never removed, but instead they’ve been left on the bookshelf to gather dust. Hence, functionality has remained pretty poor for them. Luckily, people have tackled similar problems from which this solution is derived.

There are multiple ways we can approach this, but here’s roughly how the stages of my solution works:

  1. Find and identify the object/file we want in VBA
  2. Force Excel to create a version of the file in the %temp% directory
  3. Extract information about where exactly in %temp% the file was created, by reading OLE metadata
  4. Do your Excel business with this filepath

2.1 Add Clipboard Utility code

Within your VBA project, create a new Module. For this, we’re calling it Module1 but feel free to name it something else.

What we’ll be adding to this module is code to manipulate the Windows clipboard. This is because the only way to read an OLE object’s metadata (Stage 3) is by copying the object to the clipboard, then manually reading the clipboard data. I’ll admit it’s horrendous, but the best way I could find to achieve this.

Michel Pierron had previously developed code to achieve this, and we more or less add this verbatim into our own project:

In this example, I’m going to have psuedocode to play success.wav, with a variable name of “sound_success” when a button is clicked: