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:

Imagine emailing this around

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.

Ensure “Link to file” is NOT checked, otherwise the file won’t be embedded.

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:

This wonderfully complicated code allows us to grab raw data from our clipboard in Excel. We can then sniff out important information from the clipboard. Add this to your Module.

Next, I’ve developed a GetOLETempPath function which allows us to complete Stage 3. This essentially goes through the OLE data in the clipboard and grabs where our file is.

This code literally reads the bytes of the clipboard to find our filepath. It accepts an OLE object, then calls the .Copy method to copy the object to the clipboard. The Copy method actually achieves two things: It first creates a copy of the file in a temporary directory (effectively doing Stage 2 for us), then transfers the contents of this to clipboard with additional metadata. My code then leverages the clipboard code we added earlier to extract the file location from the clipboard metadata. Vianney Philippe kindly reverse engineered the OLE structure which this is based off of. Add the above code to your module.

That’s all the code that goes into our module. Now we can get to the code that you’ll be developing yourself.

2.2 Doing your Excel Business

We now have embedded our file, and have a way to access it. Now we just need to do whatever it is we were trying to do with the file. Before we proceed, it’s a good idea to give your embedded objects proper names. This is achieved by clicking the object, then using the variable name field to give it a good name:

Use the variable name field to give your object an appropriate name

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:

This code basically finds an OLEObject called “sound_success”, then create a temporary file and grabs the filename of that temporary file, stored in a variable “EmbeddedFileName”. Note, the code above doesn’t actually play the file — you can look elsewhere for help on that.

Conclusion

Working with OLE Objects in Excel is very messy, but this method is a reliable way to poke at the contents of your file via VBA. I listed the way in which this worked above, but now that I’ve gone through the code, I can better explain how it works:

1. Find and identify the object/file we want in VBA: This is achieved by looping through all OLE Objects from the OLEObjects array
2. Force Excel to create a version of the file in the %temp% directory: A call of the .Copy method twice creates a physical file as a cache. I’m not sure why calling it once doesn’t do it, but probably some sort of assumption that it’s only worth caching if being used more than once
3. Extract information about where exactly in %temp% the file was created, by reading OLE metadata: The .Copy method copies the OLE Object to clipboard, but also stores a bunch of metadata about the object. We read the clipboard, and grab the file path from the metadata.
4. Do your Excel business with this filepath: Once we’ve grabbed the file path, we can use it like a normal file and do whatever (read-only) interactions we want.

Some notes: You’ll see from my comments that I haven’t implemented an EOF check in GetOLETempPath, because I assume you wont have any files which are a only a few bytes in size. Also, I can’t confirm that this works on a system with Unicode file paths, since it only extracts the ANSI filepath.

And that’s that. Next article I’ll go through an example of me using this technique to integrate a Hysys model into a spreadsheet elegantly. If you found this helpful, please do leave a recommendation or a comment!